﻿Imports Microsoft.VisualBasic
Imports System.Globalization
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data.OleDb

Public Class SanctionListMapping2DB

    Private SanctionListAll As ADOEntitySanctionList.SanctionListEntities

    Sub New()
        SanctionListAll = New ADOEntitySanctionList.SanctionListEntities
    End Sub

#Region "AddToEUTable"
    Public Sub AddToEUTable(ByVal rawEuXML As RawEUXMLEntity)

        If Not rawEuXML.Equals(Nothing) Then

            Try
                Dim lastImportID = (From tblM In SanctionListAll.AMLO_NEW_MASTER_TEMP Where tblM.ENTITY_ID.StartsWith("EUX") Select tblM.IMPORTID).Max
                ' delete all data before insert
                Dim dbMgr As New clsDatabase
                dbMgr.TransStart()
                Dim dummy As New Collection
                dbMgr.TransExecute("delete from KYCADMIN.RAW_EU_ADDRESS", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.RAW_EU_NAME", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_eu_birthinfo", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_eu_citizen", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_eu_passport", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.RAW_EU_MASTER", dummy)

                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_name_temp where entity_id like 'EU%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_resident_temp where entity_id like 'EU%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_address_temp where entity_id like 'EU%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_birthinfo_temp where entity_id like 'EU%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.Amlo_New_Idnumber_Temp where entity_id like 'EU%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_master_temp where entity_id like 'EU%'", dummy)
                dbMgr.TransCommit()


                Dim import2Del = (From tblIm In SanctionListAll.AMLO_NEW_IMPORT_TEMP Where tblIm.IMPORTID = lastImportID)
                If import2Del.Any Then
                    SanctionListAll.AMLO_NEW_IMPORT_TEMP.DeleteObject(import2Del.First)
                    SanctionListAll.SaveChanges()
                End If

                Dim euMaster As ADOEntitySanctionList.RAW_EU_MASTER

                Dim euEntList As List(Of RawEUXMLEntity.EUEntity) = rawEuXML.listEntity
                If euEntList.Count > 0 Then

                    ' Mapping Entity to Master
                    For Each euEnt As RawEUXMLEntity.EUEntity In euEntList
                        euMaster = New ADOEntitySanctionList.RAW_EU_MASTER
                        euMaster.ID = euEnt._id
                        euMaster.PROGRAMME = euEnt.programme
                        euMaster.REMARK = euEnt.remark
                        euMaster.TYPE = euEnt.type

                        euMaster.REG_DATE = New Date
                        DateTime.TryParseExact(euEnt.reg_date, _
                        "yyyy-MM-dd", CultureInfo.InvariantCulture, _
                        DateTimeStyles.None, euMaster.REG_DATE)

                        ' Mapping Name
                        Dim euNameList As List(Of RawEUXMLEntity.EUName) = euEnt.EUNameProperty
                        If euNameList.Count > 0 Then
                            Dim dbEUName As ADOEntitySanctionList.RAW_EU_NAME
                            For Each euName As RawEUXMLEntity.EUName In euNameList
                                dbEUName = New ADOEntitySanctionList.RAW_EU_NAME

                                dbEUName.ENTITY_ID = euEnt._id
                                dbEUName.FIRSTNAME = euName._firstName
                                dbEUName.GENDER = euName._euGender
                                dbEUName.LANGUAGE = euName._language
                                dbEUName.LASTNAME = euName._lastName
                                dbEUName.MIDDLENAME = euName._middleName
                                dbEUName.NAME_ID = euName._id
                                dbEUName.TITLE = euName._title
                                dbEUName.WHOLENAME = euName._wholeName
                                dbEUName.FUNCTION = euName._function

                                SanctionListAll.AddToRAW_EU_NAME(dbEUName)
                            Next
                        End If
                        ' Mapping Address
                        Dim euAddrList As List(Of RawEUXMLEntity.EUAddress) = euEnt.EUAddressProperty
                        If euAddrList.Count > 0 Then
                            Dim dbEUAddr As ADOEntitySanctionList.RAW_EU_ADDRESS
                            For Each euAddr As RawEUXMLEntity.EUAddress In euAddrList
                                dbEUAddr = New ADOEntitySanctionList.RAW_EU_ADDRESS

                                dbEUAddr.ENTITY_ID = euEnt._id
                                dbEUAddr.ADDR_CITY = euAddr._city
                                dbEUAddr.ADDR_COUNTRY = euAddr._country
                                dbEUAddr.ADDR_ID = euAddr._id
                                dbEUAddr.ADDR_NO = euAddr._number
                                dbEUAddr.ADDR_OTHER = euAddr._other
                                dbEUAddr.ADDR_STREET = euAddr._street
                                dbEUAddr.ADDR_ZIPCODE = euAddr._zipcode

                                SanctionListAll.AddToRAW_EU_ADDRESS(dbEUAddr)
                            Next
                        End If
                        ' Mapping BirthInfo
                        Dim euBithInfoList As List(Of RawEUXMLEntity.EUBirthInfo) = euEnt.EUBirthProperty
                        If euBithInfoList.Count > 0 Then
                            Dim dbEuBirthInfo As ADOEntitySanctionList.RAW_EU_BIRTHINFO
                            For Each euBirth As RawEUXMLEntity.EUBirthInfo In euBithInfoList
                                dbEuBirthInfo = New ADOEntitySanctionList.RAW_EU_BIRTHINFO

                                dbEuBirthInfo.ENTITY_ID = euEnt._id
                                dbEuBirthInfo.BIRTH_COUNTRY = euBirth._country
                                dbEuBirthInfo.BIRTH_DATE = euBirth._date
                                dbEuBirthInfo.BIRTH_ID = euBirth._id
                                dbEuBirthInfo.BIRTH_PLACE = euBirth._place

                                SanctionListAll.AddToRAW_EU_BIRTHINFO(dbEuBirthInfo)
                            Next
                        End If
                        ' Mapping CardInfo
                        Dim euCardInfoList As List(Of RawEUXMLEntity.EUIDCardInfo) = euEnt.EUIDCardInfoProperty
                        If euCardInfoList.Count > 0 Then
                            Dim dbEuCardInfo As ADOEntitySanctionList.RAW_EU_PASSPORT
                            For Each euCardInfo As RawEUXMLEntity.EUIDCardInfo In euCardInfoList
                                dbEuCardInfo = New ADOEntitySanctionList.RAW_EU_PASSPORT

                                dbEuCardInfo.ENTITY_ID = euEnt._id
                                dbEuCardInfo.COUNTRY = euCardInfo._country
                                dbEuCardInfo.PASSPORT_NUMBER = euCardInfo._number
                                dbEuCardInfo.PASSPORT_ID = euCardInfo._id

                                SanctionListAll.AddToRAW_EU_PASSPORT(dbEuCardInfo)
                            Next
                        End If
                        ' Mapping Citizen
                        Dim euCitizenInfoList As List(Of RawEUXMLEntity.EUCitizen) = euEnt.EUCitizenList
                        If euCitizenInfoList.Count > 0 Then
                            Dim dbCitizenInfo As ADOEntitySanctionList.RAW_EU_CITIZEN
                            For Each euCitizen As RawEUXMLEntity.EUCitizen In euCitizenInfoList
                                dbCitizenInfo = New ADOEntitySanctionList.RAW_EU_CITIZEN

                                dbCitizenInfo.ENTITY_ID = euEnt._id
                                dbCitizenInfo.COUNTRY = euCitizen._country
                                dbCitizenInfo.CTZ_ID = euCitizen._id

                                SanctionListAll.AddToRAW_EU_CITIZEN(dbCitizenInfo)
                            Next
                        End If

                        SanctionListAll.AddToRAW_EU_MASTER(euMaster)

                    Next
                    SanctionListAll.SaveChanges()

                End If
            Catch ex As Exception
                Throw New Exception("เกิดข้อผิดพลาดในการแปลงไฟล์")
            End Try
        End If
    End Sub
#End Region

#Region "addEU2TempDB"
    Public Function addEU2TempDB(ByRef rawEU As RawEUXMLEntity, ByRef fileName As String, ByRef fileType As String, ByRef dateUpload As String, ByRef userUpload As String) As String
        Const mStrSactionType As String = "EUX"

        Try

            ' Setup Variable for DB
            Dim maxName18TblSYS_ID As Integer = -1
            maxName18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_NAME_TEMP Select tblSanctionList.SYS_ID).Max
            Dim maxBith18TblSYS_ID As Integer = -1
            maxBith18TblSYS_ID = (From tblBirthInfo In SanctionListAll.AMLO_NEW_BIRTHINFO_TEMP Select tblBirthInfo.SYS_ID).Max
            Dim maxAddr18TblSYS_ID As Integer = -1
            maxAddr18TblSYS_ID = (From tblAddrInfo In SanctionListAll.AMLO_NEW_ADDRESS_TEMP Select tblAddrInfo.SYS_ID).Max
            Dim maxIDInfo18TblSYS_ID As Integer = -1
            maxIDInfo18TblSYS_ID = (From tblIDInfo In SanctionListAll.AMLO_NEW_IDNUMBER_TEMP Select tblIDInfo.SYS_ID).Max
            Dim maxNationality18TblSYS_ID As Integer = -1
            maxNationality18TblSYS_ID = (From tblNationInfo In SanctionListAll.AMLO_NEW_RESIDENT_TEMP Select tblNationInfo.SYS_ID).Max

            Dim stdDateFmt = "dd/MM/yyyy HH:mm:ss"
            ' Put data to IMPORT Table
            Dim maxImportID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IMPORT_TEMP Select tblSanctionList.IMPORTID).Max

            Dim dbImport18Tbl As New ADOEntitySanctionList.AMLO_NEW_IMPORT_TEMP
            dbImport18Tbl.IMPORTID = maxImportID + 1

            Dim importDate As New Date
            DateTime.TryParseExact(dateUpload, _
            stdDateFmt, CultureInfo.InvariantCulture, _
            DateTimeStyles.None, importDate)
            dbImport18Tbl.IMPORT_DATE = importDate

            dbImport18Tbl.FILE_NAME = fileName + "_" + dateUpload
            dbImport18Tbl.FILE_TYPE = fileType
            dbImport18Tbl.IMPORT_BY = userUpload
            SanctionListAll.AddToAMLO_NEW_IMPORT_TEMP(dbImport18Tbl)

            ' Master
            Dim dbMaster18Tbl As ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
            For Each euMaster As RawEUXMLEntity.EUEntity In rawEU.listEntity
                dbMaster18Tbl = New ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
                dbMaster18Tbl.IMPORTID = dbImport18Tbl.IMPORTID
                dbMaster18Tbl.INFO_SOURCE = mStrSactionType
                dbMaster18Tbl.ENTITY_ID = mStrSactionType + euMaster._id.ToString
                dbMaster18Tbl.EXISTING_STATUS = 0

                Dim entType As String = euMaster.type
                If Not String.IsNullOrEmpty(entType) Then
                    If entType.Equals("P") Then
                        entType = "Person"
                    ElseIf entType.Equals("E") Then
                        entType = "Entity"
                    End If
                End If
                dbMaster18Tbl.ENTITY_TYPE = entType
                dbMaster18Tbl.PROFILE_NOTES = euMaster.remark
                dbMaster18Tbl.LAST_UPDATE_DATE = dateUpload
                dbMaster18Tbl.STAMP_DTTM = rawEU.getEuDate

                ' Address
                Dim euAddressList As List(Of RawEUXMLEntity.EUAddress) = euMaster.EUAddressProperty
                If Not euAddressList Is Nothing And euAddressList.Count > 0 Then
                    Dim dbAddressList As ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                    For Each euAddr As RawEUXMLEntity.EUAddress In euAddressList
                        maxAddr18TblSYS_ID += 1
                        dbAddressList = New ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                        dbAddressList.SYS_ID = maxAddr18TblSYS_ID
                        dbAddressList.ENTITY_ID = dbMaster18Tbl.ENTITY_ID

                        dbAddressList.ADDRESS_STREET = euAddr._street
                        dbAddressList.ADDRESS_CITY = euAddr._city
                        dbAddressList.POST_CODE = euAddr._zipcode
                        If Not String.IsNullOrEmpty(euAddr._country) Then
                            dbAddressList.ADDRESS_COUNTRY = convertCountryCode(euAddr._country)
                        End If

                        ' pack all name to strAddrLine
                        Dim strAddrLine As New StringBuilder
                        Dim needCutComma As Boolean = False
                        If Not String.IsNullOrEmpty(euAddr._number) Then
                            strAddrLine.Append(euAddr._number)
                            strAddrLine.Append(",")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbAddressList.ADDRESS_STREET) Then
                            strAddrLine.Append(dbAddressList.ADDRESS_STREET)
                            strAddrLine.Append(",")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbAddressList.ADDRESS_CITY) Then
                            strAddrLine.Append(dbAddressList.ADDRESS_CITY)
                            strAddrLine.Append(",")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbAddressList.ADDRESS_COUNTRY) Then
                            strAddrLine.Append(dbAddressList.ADDRESS_COUNTRY)
                            strAddrLine.Append(",")
                            needCutComma = True
                        End If

                        If needCutComma Then
                            strAddrLine.Remove(strAddrLine.Length - 1, 1)
                            dbAddressList.ADDRESS_LINE = strAddrLine.ToString
                        End If

                        SanctionListAll.AddToAMLO_NEW_ADDRESS_TEMP(dbAddressList)
                    Next
                End If

                ' BirthInfo
                Dim euBirthInfoList As List(Of RawEUXMLEntity.EUBirthInfo) = euMaster.EUBirthProperty
                If Not euBirthInfoList Is Nothing And euBirthInfoList.Count > 0 Then
                    Dim dbBirthInfo As ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                    For Each euBirth As RawEUXMLEntity.EUBirthInfo In euBirthInfoList
                        ' TYPE 1 date only
                        ' TYPE 2 place only
                        If Not String.IsNullOrEmpty(euBirth._date) Then
                            maxBith18TblSYS_ID += 1
                            dbBirthInfo = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP

                            dbBirthInfo.ENTITY_ID = dbMaster18Tbl.ENTITY_ID
                            dbBirthInfo.SYS_ID = maxBith18TblSYS_ID
                            dbBirthInfo.INFO_TYPE = 1

                            Dim resDate As New Date
                            Dim resYear(2) As String
                            Dim bRes As Boolean = TryExtractEUDate(euBirth._date, resDate, resYear)
                            If bRes And Not resDate = Nothing Then
                                Dim sb As New StringBuilder
                                sb.Append(resDate.Day).Append("/").Append(resDate.Month).Append("/").Append(resDate.Year)
                                dbBirthInfo.BIRTHINFO_DATE = sb.ToString
                            Else
                                dbBirthInfo.RAW_BIRTH_DATE = euBirth._date
                            End If
                            SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(dbBirthInfo)

                        End If
                        If Not euBirth._place Is Nothing Then
                            maxBith18TblSYS_ID += 1
                            dbBirthInfo = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP

                            dbBirthInfo.ENTITY_ID = dbMaster18Tbl.ENTITY_ID
                            dbBirthInfo.SYS_ID = maxBith18TblSYS_ID
                            dbBirthInfo.INFO_TYPE = 2
                            dbBirthInfo.BIRTHINFO_PLACE = euBirth._place
                            SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(dbBirthInfo)

                        End If
                    Next
                End If

                ' ID Card Number
                Dim euIDList As List(Of RawEUXMLEntity.EUIDCardInfo) = euMaster.EUIDCardInfoProperty
                If Not euIDList Is Nothing Then
                    Dim dbIDInfo18Tbl As ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
                    For Each euIDInfo As RawEUXMLEntity.EUIDCardInfo In euIDList
                        dbIDInfo18Tbl = New ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
                        dbIDInfo18Tbl.ENTITY_ID = dbMaster18Tbl.ENTITY_ID

                        maxIDInfo18TblSYS_ID += 1
                        dbIDInfo18Tbl.SYS_ID = maxIDInfo18TblSYS_ID
                        Dim strIDValue As String = ""
                        Dim strIDType As String = ""

                        extractEUNameCard(euIDInfo._number, strIDValue, strIDType)

                        dbIDInfo18Tbl.ID_TYPE_CODE = 0
                        dbIDInfo18Tbl.ID_TYPE = strIDType
                        dbIDInfo18Tbl.ID_VALUE = strIDValue
                        If Not String.IsNullOrEmpty(euIDInfo._country) Then
                            dbIDInfo18Tbl.ID_COUNTRY = convertCountryCode(euIDInfo._country)
                        End If

                        SanctionListAll.AddToAMLO_NEW_IDNUMBER_TEMP(dbIDInfo18Tbl)
                    Next
                End If

                ' Name
                Dim euNameList As List(Of RawEUXMLEntity.EUName) = euMaster.EUNameProperty
                ' Check Nullable Name List
                If Not euNameList Is Nothing And euNameList.Count > 0 Then
                    Dim dbName18Tbl As ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                    For Each euName As RawEUXMLEntity.EUName In euNameList
                        dbName18Tbl = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                        dbName18Tbl.ENTITY_ID = dbMaster18Tbl.ENTITY_ID
                        maxName18TblSYS_ID += 1
                        dbName18Tbl.SYS_ID = maxName18TblSYS_ID
                        dbName18Tbl.NAME_TYPE = "Primary"

                        If dbMaster18Tbl.ENTITY_TYPE.Equals("Entity") Then
                            If IsSpaceAlphaNumeric(euName._wholeName) Then
                                dbName18Tbl.ENTITY_NAME_E = euName._wholeName
                            Else
                                dbName18Tbl.ENTITY_NAME = euName._wholeName
                            End If

                        Else
                            Dim firstName = euName._firstName
                            Dim middleName = euName._middleName
                            Dim lastName = euName._lastName
                            Dim title = euName._title

                            If IsSpaceAlphaNumeric(firstName) And IsSpaceAlphaNumeric(middleName) And IsSpaceAlphaNumeric(lastName) Then
                                dbName18Tbl.FIRSTNAME_E = firstName
                                dbName18Tbl.MIDDLE_NAME_E = middleName
                                dbName18Tbl.SURNAME_E = lastName
                            Else
                                dbName18Tbl.FIRST_NAME = firstName
                                dbName18Tbl.MIDDLE_NAME = middleName
                                dbName18Tbl.SURNAME = lastName
                            End If

                            If IsSpaceAlphaNumeric(title) Then
                                dbName18Tbl.SUFFIX_E = title
                            Else
                                dbName18Tbl.SUFFIX = title
                            End If

                        End If

                        ' pack all name to singleStringName
                        Dim strSinglename As New StringBuilder
                        Dim needCutComma As Boolean = False
                        If Not String.IsNullOrEmpty(dbName18Tbl.FIRSTNAME_E) Then
                            strSinglename.Append(dbName18Tbl.FIRSTNAME_E)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbName18Tbl.MIDDLE_NAME_E) Then
                            strSinglename.Append(dbName18Tbl.MIDDLE_NAME_E)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbName18Tbl.SURNAME_E) Then
                            strSinglename.Append(dbName18Tbl.SURNAME_E)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(euName._wholeName) And IsSpaceAlphaNumeric(euName._wholeName) Then
                            strSinglename.Append(euName._wholeName)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If

                        If needCutComma Then
                            strSinglename.Remove(strSinglename.Length - 1, 1)
                            Dim strSingleStringName = strSinglename.ToString

                            'If IsSpaceAlphaNumeric(strSingleStringName) Then
                            dbName18Tbl.SINGLE_STRING_NAME = strSingleStringName
                            'Else
                            '    dbName18Tbl.ORIGINAL_SCRIPT_NAME = strSingleStringName
                            'End If
                        End If

                        ' pack all name to original Script name
                        strSinglename = New StringBuilder
                        needCutComma = False
                        If Not String.IsNullOrEmpty(dbName18Tbl.FIRST_NAME) Then
                            strSinglename.Append(dbName18Tbl.FIRST_NAME)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbName18Tbl.MIDDLE_NAME) Then
                            strSinglename.Append(dbName18Tbl.MIDDLE_NAME)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(dbName18Tbl.SURNAME) Then
                            strSinglename.Append(dbName18Tbl.SURNAME)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(euName._wholeName) And Not IsSpaceAlphaNumeric(euName._wholeName) Then
                            strSinglename.Append(euName._wholeName)
                            strSinglename.Append("/")
                            needCutComma = True
                        End If


                        If needCutComma Then
                            strSinglename.Remove(strSinglename.Length - 1, 1)
                            Dim strSingleStringName = strSinglename.ToString

                            'If IsSpaceAlphaNumeric(strSingleStringName) Then
                            '    dbName18Tbl.SINGLE_STRING_NAME = strSingleStringName
                            'Else
                            dbName18Tbl.ORIGINAL_SCRIPT_NAME = strSingleStringName
                            'End If
                        End If

                        dbName18Tbl.INFO_SOURCE = mStrSactionType
                        If Not String.IsNullOrEmpty(euName._euGender) Then
                            dbMaster18Tbl.GENDER = euName._euGender
                        End If
                        SanctionListAll.AddToAMLO_NEW_NAME_TEMP(dbName18Tbl)
                    Next
                End If

                ' Nationality
                Dim euNationlityList As List(Of RawEUXMLEntity.EUCitizen) = euMaster.EUCitizenList
                If Not euNationlityList Is Nothing Then
                    Dim dbNationality18Tbl As ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                    For Each euNation As RawEUXMLEntity.EUCitizen In euNationlityList
                        dbNationality18Tbl = New ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                        maxNationality18TblSYS_ID += 1
                        dbNationality18Tbl.SYS_ID = maxNationality18TblSYS_ID
                        dbNationality18Tbl.ENTITY_ID = dbMaster18Tbl.ENTITY_ID

                        If Not String.IsNullOrEmpty(euNation._country) Then
                            dbNationality18Tbl.COUNTRY_DESC = convertCountryCode(euNation._country)
                        End If

                        SanctionListAll.AddToAMLO_NEW_RESIDENT_TEMP(dbNationality18Tbl)
                    Next
                End If
                SanctionListAll.AddToAMLO_NEW_MASTER_TEMP(dbMaster18Tbl)
            Next
            SanctionListAll.SaveChanges()
        Catch ex As Exception
            Throw New Exception("เกิดข้อผิดพลาดในการแปลงไฟล์")
        End Try
        Return mStrSactionType

    End Function
#End Region

#Region "addOFACTable"
    Public Function addOFACTable(ByRef rawOFAC As RawOFACXMLEntity, ByRef fileName As String, ByRef fileType As String, ByRef dateUpload As String, ByRef userUpload As String) As String
        Const mStrSactionType As String = "FCX"
        Const mStrInfoSanctionType As String = "OFACX"
        Try
            If Not rawOFAC Is Nothing Then
                ' delete all data before insert

                Dim lastImportID = (From tblM In SanctionListAll.AMLO_NEW_MASTER_TEMP Where tblM.ENTITY_ID.StartsWith(mStrSactionType) Select tblM.IMPORTID).Max

                Dim dbMgr As New clsDatabase
                dbMgr.TransStart()
                Dim dummy As New Collection
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_address", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_citizenship", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_nationality", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_pob", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_dob", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_akainfo", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_vesselinfo", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_idinfo", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_program", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.raw_ofac_master", dummy)

                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_name_temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_resident_temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_vessel_temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_address_temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_birthinfo_temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.Amlo_New_Idnumber_Temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_master_temp where entity_id like '" + mStrSactionType + "%'", dummy)
                dbMgr.TransExecute("delete from KYCADMIN.amlo_new_import_temp where importid = '" & lastImportID & "'", dummy)

                dbMgr.TransCommit()
                ' end delete

                ' =Set up variable
                ' ==Max 18tbl name index
                Dim maxName18TblSYS_ID As Integer = -1
                maxName18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_NAME_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18Tble resident index
                Dim maxResident18TblSYS_ID As Integer = -1
                maxResident18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_RESIDENT_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18Tble vessel index
                Dim maxVessel18TblSYS_ID As Integer = -1
                maxVessel18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_VESSEL_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18Tble address index
                Dim maxAddr18TblSYS_ID As Integer = -1
                maxAddr18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_ADDRESS_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18Tble birthDay index
                Dim maxBirth18TblSYS_ID As Integer = -1
                maxBirth18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_BIRTHINFO_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18Tble IDNubmer index
                Dim maxIDNumber18TblSYS_ID As Integer = -1
                maxIDNumber18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IDNUMBER_TEMP Select tblSanctionList.SYS_ID).Max

                ' ==Max Temp Program Table index
                Dim maxPGMTempSYS_ID As Integer = -1
                maxPGMTempSYS_ID = (From tblSanctionList In SanctionListAll.RAW_OFAC_PROGRAM Select tblSanctionList.SYS_ID).Max

                '-- Declaration of raw OFAC table
                Dim dbFCMaster As ADOEntitySanctionList.RAW_OFAC_MASTER
                Dim dbFCAKA As ADOEntitySanctionList.RAW_OFAC_AKAINFO
                Dim dbFCNational As ADOEntitySanctionList.RAW_OFAC_NATIONALITY
                Dim dbFCCTZS As ADOEntitySanctionList.RAW_OFAC_CITIZENSHIP
                Dim dbFCVessel As ADOEntitySanctionList.RAW_OFAC_VESSELINFO
                Dim dbFCAddr As ADOEntitySanctionList.RAW_OFAC_ADDRESS
                Dim dbFCDOB As ADOEntitySanctionList.RAW_OFAC_DOB
                Dim dbFCPOB As ADOEntitySanctionList.RAW_OFAC_POB
                Dim dbPGM As ADOEntitySanctionList.RAW_OFAC_PROGRAM
                Dim dbFCIDNumber As ADOEntitySanctionList.RAW_OFAC_IDINFO
                '-- Declaration of temp OFAC table
                Dim db18TblMaster As ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
                Dim db18TblName As ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                Dim db18TblResident As ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                Dim db18TblVessel As ADOEntitySanctionList.AMLO_NEW_VESSEL_TEMP
                Dim db18TblAddr As ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                Dim db18TbBirthInfo As ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                Dim db18TblIdInfo As ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP

                'IMPORT Table
                Dim stdDateFmt = "dd/MM/yyyy HH:mm:ss"
                Dim maxImportID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IMPORT_TEMP Select tblSanctionList.IMPORTID).Max
                Dim dbImport18Tbl As New ADOEntitySanctionList.AMLO_NEW_IMPORT_TEMP
                dbImport18Tbl.IMPORTID = maxImportID + 1

                Dim importDate As New Date
                DateTime.TryParseExact(dateUpload, _
                stdDateFmt, CultureInfo.InvariantCulture, _
                DateTimeStyles.None, importDate)
                dbImport18Tbl.IMPORT_DATE = importDate

                dbImport18Tbl.FILE_NAME = fileName + "_" + dateUpload
                dbImport18Tbl.FILE_TYPE = fileType
                dbImport18Tbl.IMPORT_BY = userUpload
                SanctionListAll.AddToAMLO_NEW_IMPORT_TEMP(dbImport18Tbl)

                Dim fcEntList As List(Of RawOFACXMLEntity.SDNEntity) = rawOFAC.SDNListProperty
                If fcEntList.Count > 0 Then
                    For Each fcEnt As RawOFACXMLEntity.SDNEntity In fcEntList
                        ' Raw Master Tbl
                        dbFCMaster = New ADOEntitySanctionList.RAW_OFAC_MASTER
                        dbFCMaster.FIRSTNAME = fcEnt.firstName
                        dbFCMaster.ID = fcEnt.uid
                        dbFCMaster.LASTNAME = fcEnt.lastName
                        dbFCMaster.REMARKS = fcEnt.remarks
                        dbFCMaster.SDNTYPE = fcEnt.type
                        dbFCMaster.TITLE = fcEnt.title

                        ' Temp Master Tbl
                        db18TblMaster = New ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
                        db18TblMaster.IMPORTID = dbImport18Tbl.IMPORTID
                        db18TblMaster.ENTITY_ID = mStrSactionType + fcEnt.uid.ToString
                        db18TblMaster.INFO_SOURCE = mStrInfoSanctionType
                        db18TblMaster.EXISTING_STATUS = 0
                        Dim enttype As String = fcEnt.type
                        If Not String.IsNullOrEmpty(enttype) And enttype.Equals("Individual") Then
                            enttype = "Person"
                        End If
                        db18TblMaster.ENTITY_TYPE = enttype
                        db18TblMaster.GENDER = "Other"
                        db18TblMaster.PROFILE_NOTES = fcEnt.remarks
                        db18TblMaster.LAST_UPDATE_DATE = dateUpload
                        db18TblMaster.STAMP_DTTM = rawOFAC.publishInfo.getFormatOFACDateTimeStamp

                        ' Temp Name Tbl
                        db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                        db18TblName.ENTITY_ID = db18TblMaster.ENTITY_ID
                        maxName18TblSYS_ID += 1
                        db18TblName.SYS_ID = maxName18TblSYS_ID

                        db18TblName.NAME_TYPE = "Primary Name"
                        db18TblName.INFO_SOURCE = mStrInfoSanctionType
                        If db18TblMaster.ENTITY_TYPE.Equals("Person") Then

                            If IsSpaceAlphaNumeric(fcEnt.title) Then
                                db18TblName.SUFFIX_E = fcEnt.title
                            Else
                                db18TblName.SUFFIX = fcEnt.title
                            End If

                            If IsSpaceAlphaNumeric(fcEnt.firstName) And IsSpaceAlphaNumeric(fcEnt.lastName) Then

                                db18TblName.FIRSTNAME_E = fcEnt.firstName
                                db18TblName.SURNAME_E = fcEnt.lastName
                            Else
                                db18TblName.FIRST_NAME = fcEnt.firstName
                                db18TblName.SURNAME = fcEnt.lastName
                            End If
                        Else
                            If IsSpaceAlphaNumeric(fcEnt.lastName) Then
                                db18TblName.ENTITY_NAME_E = fcEnt.lastName
                            Else
                                db18TblName.ENTITY_NAME = fcEnt.lastName
                            End If
                        End If
                        Dim sb As New StringBuilder
                        Dim needCutComma As Boolean
                        If Not String.IsNullOrEmpty(fcEnt.firstName) Then
                            sb.Append(fcEnt.firstName + "/")
                            needCutComma = True
                        End If
                        If Not String.IsNullOrEmpty(fcEnt.lastName) Then
                            sb.Append(fcEnt.lastName + "/")
                            needCutComma = True
                        End If
                        If needCutComma Then
                            sb.Remove(sb.Length - 1, 1)
                            If IsSpaceAlphaNumeric(sb.ToString) Then
                                db18TblName.SINGLE_STRING_NAME = sb.ToString
                            Else
                                db18TblName.ORIGINAL_SCRIPT_NAME = sb.ToString
                            End If
                        End If

                        SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)

                        If Not fcEnt.akaList Is Nothing Then
                            Dim listAka As List(Of RawOFACXMLEntity.AkaList.Aka) = fcEnt.akaList.akaListEntity
                            If Not listAka Is Nothing And listAka.Count > 0 Then
                                For Each fcAka As RawOFACXMLEntity.AkaList.Aka In listAka
                                    ' Temp Name Tbl (aka type)
                                    db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                                    db18TblName.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    maxName18TblSYS_ID += 1
                                    db18TblName.SYS_ID = maxName18TblSYS_ID
                                    db18TblName.NAME_TYPE = fcAka.type
                                    db18TblName.INFO_SOURCE = mStrInfoSanctionType
                                    db18TblName.CATEGORY_ = fcAka.category
                                    If db18TblMaster.ENTITY_TYPE.Equals("Person") Then
                                        If IsSpaceAlphaNumeric(fcAka.firstName) And IsSpaceAlphaNumeric(fcAka.lastName) Then

                                            db18TblName.FIRSTNAME_E = fcAka.firstName
                                            db18TblName.SURNAME_E = fcAka.lastName
                                        Else
                                            db18TblName.FIRST_NAME = fcAka.firstName
                                            db18TblName.SURNAME = fcAka.lastName
                                        End If
                                    Else
                                        If IsSpaceAlphaNumeric(fcAka.lastName) Then
                                            db18TblName.ENTITY_NAME_E = fcAka.lastName
                                        Else
                                            db18TblName.ENTITY_NAME = fcAka.lastName
                                        End If
                                    End If

                                    sb = New StringBuilder
                                    needCutComma = False
                                    If Not String.IsNullOrEmpty(fcAka.firstName) Then
                                        sb.Append(fcAka.firstName + "/")
                                        needCutComma = True
                                    End If
                                    If Not String.IsNullOrEmpty(fcAka.lastName) Then
                                        sb.Append(fcAka.lastName + "/")
                                        needCutComma = True
                                    End If
                                    If needCutComma Then
                                        sb.Remove(sb.Length - 1, 1)
                                        If IsSpaceAlphaNumeric(sb.ToString) Then
                                            db18TblName.SINGLE_STRING_NAME = sb.ToString
                                        Else
                                            db18TblName.ORIGINAL_SCRIPT_NAME = sb.ToString
                                        End If
                                    End If

                                    SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)

                                    ' Raw aka Tbl
                                    dbFCAKA = New ADOEntitySanctionList.RAW_OFAC_AKAINFO
                                    dbFCAKA.CATEGORY = fcAka.category
                                    dbFCAKA.FIRSTNAME = fcAka.firstName
                                    dbFCAKA.LASTNAME = fcAka.lastName
                                    dbFCAKA.SYS_ID = fcAka.uid
                                    dbFCAKA.SDN_ID = dbFCMaster.ID
                                    dbFCAKA.TYPE = fcAka.type

                                    SanctionListAll.AddToRAW_OFAC_AKAINFO(dbFCAKA)
                                Next
                            End If
                        End If

                        ' Raw Program Table
                        If Not fcEnt.programListEntity Is Nothing Then
                            If Not fcEnt.programListEntity.program Is Nothing Then
                                Dim pgmList As List(Of String) = fcEnt.programListEntity.program
                                For Each pgmName As String In pgmList
                                    dbPGM = New ADOEntitySanctionList.RAW_OFAC_PROGRAM
                                    dbPGM.SDN_ID = dbFCMaster.ID
                                    maxPGMTempSYS_ID += 1
                                    dbPGM.SYS_ID = maxPGMTempSYS_ID
                                    dbPGM.PROGRAM_TEXT = pgmName

                                    SanctionListAll.AddToRAW_OFAC_PROGRAM(dbPGM)
                                Next
                            End If

                        End If

                        ' Temp and RaW Nationality Tbl
                        If Not fcEnt.nationList Is Nothing Then
                            If Not fcEnt.nationList.nationList Is Nothing Then
                                Dim listNational As List(Of RawOFACXMLEntity.NationalityInfo.NationalityList) = fcEnt.nationList.nationList
                                For Each rawNation As RawOFACXMLEntity.NationalityInfo.NationalityList In listNational

                                    dbFCNational = New ADOEntitySanctionList.RAW_OFAC_NATIONALITY
                                    dbFCNational.SYS_ID = rawNation.uid
                                    dbFCNational.SDN_ID = dbFCMaster.ID
                                    dbFCNational.MAINENTRY = rawNation.mainEntry
                                    dbFCNational.COUNTRY = rawNation.country

                                    SanctionListAll.AddToRAW_OFAC_NATIONALITY(dbFCNational)

                                    db18TblResident = New ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                                    db18TblResident.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    maxResident18TblSYS_ID += 1
                                    db18TblResident.SYS_ID = maxResident18TblSYS_ID
                                    db18TblResident.TYPE = 1
                                    db18TblResident.COUNTRY_TYPE = "Nationality"
                                    db18TblResident.COUNTRY_DESC = rawNation.country
                                    Dim intMainEntry As Integer = 1
                                    If rawNation.mainEntry.ToLower.Equals("false") Then
                                        intMainEntry = 0
                                    End If

                                    db18TblResident.MAIN_ENTRY = intMainEntry
                                    SanctionListAll.AddToAMLO_NEW_RESIDENT_TEMP(db18TblResident)
                                Next
                            End If

                        End If

                        ' Temp and RaW Citizen Tbl
                        If Not fcEnt.citizenshipList Is Nothing Then
                            If Not fcEnt.citizenshipList.citizenList Is Nothing Then
                                Dim rawCTZList As List(Of RawOFACXMLEntity.CitizenshipList.Citizenship) = fcEnt.citizenshipList.citizenList
                                For Each ctz As RawOFACXMLEntity.CitizenshipList.Citizenship In rawCTZList
                                    dbFCCTZS = New ADOEntitySanctionList.RAW_OFAC_CITIZENSHIP
                                    dbFCCTZS.COUNTRY = ctz.country
                                    dbFCCTZS.SDN_ID = dbFCMaster.ID
                                    dbFCCTZS.SYS_ID = ctz.uid
                                    dbFCCTZS.MAINENTRY = ctz.mainEntry
                                    SanctionListAll.AddToRAW_OFAC_CITIZENSHIP(dbFCCTZS)

                                    db18TblResident = New ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                                    db18TblResident.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    maxResident18TblSYS_ID += 1
                                    db18TblResident.SYS_ID = maxResident18TblSYS_ID
                                    db18TblResident.TYPE = 1
                                    db18TblResident.COUNTRY_TYPE = "Citizenship"
                                    db18TblResident.COUNTRY_DESC = ctz.country
                                    Dim intMainEntry As Integer = 1
                                    If ctz.mainEntry.ToLower.Equals("false") Then
                                        intMainEntry = 0
                                    End If

                                    db18TblResident.MAIN_ENTRY = intMainEntry
                                    SanctionListAll.AddToAMLO_NEW_RESIDENT_TEMP(db18TblResident)
                                Next
                            End If
                        End If

                        ' Temp and Raw Vessel info
                        If Not fcEnt.vesselInfo Is Nothing Then
                            Dim vesselEnt As RawOFACXMLEntity.VesselInfo = fcEnt.vesselInfo
                            dbFCVessel = New ADOEntitySanctionList.RAW_OFAC_VESSELINFO
                            dbFCVessel.SDN_ID = dbFCMaster.ID
                            maxVessel18TblSYS_ID += 1
                            dbFCVessel.SYS_ID = maxVessel18TblSYS_ID
                            dbFCVessel.GROSSREGISTEREDTONNAGE = vesselEnt.grossReg
                            dbFCVessel.TONNAGE = vesselEnt.tonnage
                            dbFCVessel.VESSELFLAG = vesselEnt.vesselFlag
                            dbFCVessel.VESSELOWNER = vesselEnt.vesselVesselOwner
                            dbFCVessel.VESSELTYPE = vesselEnt.vesselType
                            dbFCVessel.CALLSIGN = vesselEnt.callSign

                            SanctionListAll.AddToRAW_OFAC_VESSELINFO(dbFCVessel)

                            db18TblVessel = New ADOEntitySanctionList.AMLO_NEW_VESSEL_TEMP
                            db18TblVessel.SYS_ID = maxVessel18TblSYS_ID
                            db18TblVessel.ENTITY_ID = db18TblMaster.ENTITY_ID
                            db18TblVessel.VESSEL_CALL_SIGN = vesselEnt.callSign
                            db18TblVessel.VESSEL_FLAG = vesselEnt.vesselFlag
                            db18TblVessel.VESSEL_GRT = vesselEnt.grossReg
                            db18TblVessel.VESSEL_OWNER = vesselEnt.vesselVesselOwner
                            db18TblVessel.VESSEL_TONNAGE = vesselEnt.tonnage
                            db18TblVessel.VESSEL_TYPE = vesselEnt.vesselType

                            SanctionListAll.AddToAMLO_NEW_VESSEL_TEMP(db18TblVessel)

                        End If

                        ' Temp and Raw Address
                        If Not fcEnt.addrList Is Nothing Then
                            If Not fcEnt.addrList.addrList Is Nothing Then
                                Dim addrList As List(Of RawOFACXMLEntity.AddressInfo.AddressDetail) = fcEnt.addrList.addrList
                                For Each addrEnt As RawOFACXMLEntity.AddressInfo.AddressDetail In addrList
                                    dbFCAddr = New ADOEntitySanctionList.RAW_OFAC_ADDRESS
                                    dbFCAddr.SDN_ID = dbFCMaster.ID
                                    dbFCAddr.SYS_ID = addrEnt.uid
                                    dbFCAddr.ADDRESS1 = addrEnt.address1
                                    dbFCAddr.ADDRESS2 = addrEnt.address2
                                    dbFCAddr.ADDRESS3 = addrEnt.address3
                                    dbFCAddr.CITY = addrEnt.city
                                    dbFCAddr.COUNTRY = addrEnt.country
                                    dbFCAddr.POSTALCODE = addrEnt.postCode
                                    dbFCAddr.STATEORPROVINCE = addrEnt.stateOrProvince

                                    SanctionListAll.AddToRAW_OFAC_ADDRESS(dbFCAddr)

                                    db18TblAddr = New ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                                    maxAddr18TblSYS_ID += 1
                                    db18TblAddr.SYS_ID = maxAddr18TblSYS_ID
                                    db18TblAddr.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    sb = New StringBuilder()
                                    needCutComma = False
                                    If Not String.IsNullOrEmpty(addrEnt.address1) Then
                                        sb.Append(addrEnt.address1).Append(",")
                                        needCutComma = True
                                        Dim addr1 As String = addrEnt.address1
                                        If addrEnt.address1.Length > 100 Then
                                            addr1 = addr1.Substring(0, 100)
                                        End If
                                        db18TblAddr.ADDRESS_1 = addr1
                                    End If
                                    If Not String.IsNullOrEmpty(addrEnt.address2) Then
                                        sb.Append(addrEnt.address2).Append(",")
                                        needCutComma = True
                                        db18TblAddr.ADDRESS_2 = addrEnt.address2
                                    End If
                                    If Not String.IsNullOrEmpty(addrEnt.address3) Then
                                        sb.Append(addrEnt.address3).Append(",")
                                        needCutComma = True
                                        db18TblAddr.ADDRESS_3 = addrEnt.address2
                                    End If
                                    If Not String.IsNullOrEmpty(addrEnt.city) Then
                                        sb.Append(addrEnt.city).Append(",")
                                        needCutComma = True
                                        db18TblAddr.ADDRESS_CITY = addrEnt.city
                                    End If
                                    If Not String.IsNullOrEmpty(addrEnt.stateOrProvince) Then
                                        sb.Append(addrEnt.stateOrProvince).Append(",")
                                        needCutComma = True
                                        db18TblAddr.ADDRESS_STATE = addrEnt.stateOrProvince
                                    End If
                                    If Not String.IsNullOrEmpty(addrEnt.country) Then
                                        sb.Append(addrEnt.country).Append(",")
                                        needCutComma = True
                                        db18TblAddr.ADDRESS_COUNTRY = addrEnt.country
                                    End If
                                    If Not String.IsNullOrEmpty(addrEnt.postCode) Then
                                        sb.Append(addrEnt.postCode).Append(",")
                                        needCutComma = True
                                        db18TblAddr.POST_CODE = addrEnt.postCode
                                    End If

                                    If needCutComma Then
                                        sb.Remove(sb.Length - 1, 1)
                                        db18TblAddr.ADDRESS_LINE = sb.ToString
                                    End If

                                    SanctionListAll.AddToAMLO_NEW_ADDRESS_TEMP(db18TblAddr)
                                Next

                            End If
                        End If

                        ' Temp and Raw BirthInfo table (Birth Date)
                        If Not fcEnt.dobList Is Nothing Then
                            Dim listFCBirthInFo As List(Of RawOFACXMLEntity.DoBInfo.DobDetail) = fcEnt.dobList.dobList
                            If Not listFCBirthInFo Is Nothing And listFCBirthInFo.Count > 0 Then
                                For Each dobEnt As RawOFACXMLEntity.DoBInfo.DobDetail In listFCBirthInFo
                                    dbFCDOB = New ADOEntitySanctionList.RAW_OFAC_DOB
                                    dbFCDOB.SYS_ID = dobEnt.uid
                                    dbFCDOB.SDN_ID = dbFCMaster.ID
                                    dbFCDOB.DATEOFBIRTH = dobEnt.dob
                                    dbFCDOB.MAINENTRY = dobEnt.mainEntry

                                    SanctionListAll.AddToRAW_OFAC_DOB(dbFCDOB)

                                    db18TbBirthInfo = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                                    maxBirth18TblSYS_ID += 1
                                    db18TbBirthInfo.SYS_ID = maxBirth18TblSYS_ID
                                    db18TbBirthInfo.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    db18TbBirthInfo.INFO_TYPE = 1
                                    db18TbBirthInfo.MAIN_ENTRY = dobEnt.MainEntryDigitProperty
                                    Dim strDate As String = TryExactOFACDate(dobEnt.dob)
                                    If String.IsNullOrEmpty(strDate) Then
                                        db18TbBirthInfo.RAW_BIRTH_DATE = dobEnt.dob
                                    Else
                                        db18TbBirthInfo.BIRTHINFO_DATE = strDate
                                    End If

                                    SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TbBirthInfo)
                                Next
                            End If
                        End If

                        ' Temp and Raw BirthInfo table (Birth Place)
                        If Not fcEnt.pobList Is Nothing Then
                            If Not fcEnt.pobList.pobList Is Nothing Then
                                For Each pobEnt As RawOFACXMLEntity.PoBInfo.PoBDetail In fcEnt.pobList.pobList
                                    dbFCPOB = New ADOEntitySanctionList.RAW_OFAC_POB
                                    dbFCPOB.SDN_ID = dbFCMaster.ID
                                    dbFCPOB.SYS_ID = pobEnt.uid
                                    dbFCPOB.PLACEOFBIRTH = pobEnt.pob
                                    dbFCPOB.MAINENTRY = pobEnt.mainEntry

                                    SanctionListAll.AddToRAW_OFAC_POB(dbFCPOB)

                                    db18TbBirthInfo = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                                    maxBirth18TblSYS_ID += 1
                                    db18TbBirthInfo.SYS_ID = maxBirth18TblSYS_ID
                                    db18TbBirthInfo.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    db18TbBirthInfo.INFO_TYPE = 2
                                    db18TbBirthInfo.BIRTHINFO_PLACE = pobEnt.pob
                                    db18TbBirthInfo.MAIN_ENTRY = pobEnt.MainEntryDigitProperty
                                    SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TbBirthInfo)
                                Next
                            End If
                        End If

                        ' Temp and Raw ID Information table
                        If Not fcEnt.idListEntity Is Nothing Then
                            If Not fcEnt.idListEntity.idEntity Is Nothing Then
                                For Each idEnt As RawOFACXMLEntity.IDList.IDInfo In fcEnt.idListEntity.idEntity
                                    dbFCIDNumber = New ADOEntitySanctionList.RAW_OFAC_IDINFO
                                    dbFCIDNumber.SDN_ID = dbFCMaster.ID
                                    dbFCIDNumber.SYS_ID = idEnt.uid
                                    dbFCIDNumber.EXPIRATIONDATE = TryExactOFACDate2Date(idEnt.expDate)
                                    dbFCIDNumber.IDCOUNTRY = idEnt.idCountry
                                    dbFCIDNumber.IDTYPE = idEnt.idType
                                    dbFCIDNumber.ISSUEDATE = TryExactOFACDate2Date(idEnt.issueDate)

                                    SanctionListAll.AddToRAW_OFAC_IDINFO(dbFCIDNumber)

                                    db18TblIdInfo = New ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
                                    maxIDNumber18TblSYS_ID += 1
                                    db18TblIdInfo.SYS_ID = maxIDNumber18TblSYS_ID
                                    db18TblIdInfo.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    db18TblIdInfo.ID_TYPE_CODE = 0
                                    db18TblIdInfo.ID_TYPE = idEnt.idType
                                    db18TblIdInfo.ID_VALUE = removeSpecialChar(idEnt.idNumber)
                                    db18TblIdInfo.ID_COUNTRY = idEnt.idCountry
                                    Dim rawIssueDate As String = TryExactOFACDate(idEnt.issueDate)
                                    If rawIssueDate Is Nothing Then
                                        rawIssueDate = idEnt.issueDate
                                    End If
                                    db18TblIdInfo.ID_ISSUEDATE = rawIssueDate

                                    Dim rawExpDate As String = TryExactOFACDate(idEnt.expDate)
                                    If rawExpDate Is Nothing Then
                                        rawExpDate = idEnt.expDate
                                    End If
                                    db18TblIdInfo.ID_EXPIRYDATE = rawExpDate

                                    SanctionListAll.AddToAMLO_NEW_IDNUMBER_TEMP(db18TblIdInfo)

                                Next
                            End If
                        End If

                        SanctionListAll.AddToRAW_OFAC_MASTER(dbFCMaster)
                        SanctionListAll.AddToAMLO_NEW_MASTER_TEMP(db18TblMaster)
                    Next

                End If
                SanctionListAll.SaveChanges()
            End If
        Catch ex As Exception
            Throw New Exception("เกิดข้อผิดพลาดในการแปลงไฟล์")
        End Try

        Return mStrInfoSanctionType
    End Function
#End Region

#Region "addToUNTable"
    Public Function addToUNTable(ByVal rawUN As RawUNXMLEntity, ByRef fileName As String, ByRef fileType As String, ByRef dateUpload As String, ByRef userUpload As String) As String
        Const mStrSactionType As String = "UNX"
        Dim sanctionAlll As New ADOEntitySanctionList.SanctionListEntities
        Try
            ' delete all data before insert

            Dim lastImportID = (From tblM In sanctionAlll.AMLO_NEW_MASTER_TEMP Where tblM.ENTITY_ID.StartsWith(mStrSactionType) Select tblM.IMPORTID).Max

            Dim dbMgr As New clsDatabase
            dbMgr.TransStart()
            Dim dummy As New Collection
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_address", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_alias", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_dob", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_doc", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_nationality", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_pob", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_title", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.raw_un_master", dummy)

            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_name_temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_resident_temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_vessel_temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_address_temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_birthinfo_temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.Amlo_New_Idnumber_Temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_master_temp where entity_id like '" + mStrSactionType + "%'", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_new_import_temp where importid = '" & lastImportID & "'", dummy)

            dbMgr.TransCommit()
            ' end delete



            ' TODO : Test
            Dim maxLenght As Integer = 0

            ' raw UN
            If Not rawUN Is Nothing Then

                ' Initial variable
                ' Raw Tbl
                Dim dbUNIndividual As ADOEntitySanctionList.RAW_UN_MASTER
                Dim dbUNAlias As ADOEntitySanctionList.RAW_UN_ALIAS
                Dim dbUNTitle As ADOEntitySanctionList.RAW_UN_TITLE
                Dim dbUNBirthInfo As ADOEntitySanctionList.RAW_UN_DOB
                Dim dbUNPOBInfo As ADOEntitySanctionList.RAW_UN_POB
                Dim dbUNAddrinfo As ADOEntitySanctionList.RAW_UN_ADDRESS
                Dim dbUNNationality As ADOEntitySanctionList.RAW_UN_NATIONALITY
                Dim dbUNDocument As ADOEntitySanctionList.RAW_UN_DOC
                ' Temp Tbl
                Dim db18TblMaster As ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
                Dim db18TblName As ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                Dim db18TblBirth As ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                Dim db18TblAddress As ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                Dim db18TblResident As ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                Dim db18TblIDNumber As ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
                ' Max ID
                ' ==Max 18tbl name index
                Dim maxName18TblSYS_ID As Integer = -1
                maxName18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_NAME_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18tbl birthInfo index
                Dim maxDob18TblSYS_ID As Integer = -1
                maxDob18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_BIRTHINFO_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18tbl Address index
                Dim maxAddr18TblSYS_ID As Integer = -1
                maxAddr18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_ADDRESS_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18tbl Resident index
                Dim maxResident18TblSYS_ID As Integer = -1
                maxResident18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_RESIDENT_TEMP Select tblSanctionList.SYS_ID).Max
                ' ==Max 18tbl ID Number index
                Dim maxIDNumber18TblSYS_ID As Integer = -1
                maxIDNumber18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IDNUMBER_TEMP Select tblSanctionList.SYS_ID).Max

                ' ==Max Raw UN index
                ' Title
                Dim maxTitleUNSYS_ID As Integer = -1
                maxTitleUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_TITLE Select tblSanctionList.SYS_ID).Max
                ' Alias
                Dim maxAliasUNSYS_ID As Integer = -1
                maxAliasUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_ALIAS Select tblSanctionList.SYS_ID).Max
                ' DOB
                Dim maxDOBUNSYS_ID As Integer = -1
                maxDOBUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_DOB Select tblSanctionList.SYS_ID).Max
                ' POB
                Dim maxPOBUNSYS_ID As Integer = -1
                maxPOBUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_POB Select tblSanctionList.SYS_ID).Max
                ' Address
                Dim maxAddrUNSYS_ID As Integer = -1
                maxAddrUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_ADDRESS Select tblSanctionList.SYS_ID).Max
                ' Resident
                Dim maxNationalityUNSYS_ID As Integer = -1
                maxNationalityUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_NATIONALITY Select tblSanctionList.SYS_ID).Max
                ' Documet
                Dim maxDocUNSYS_ID As Integer = -1
                maxDocUNSYS_ID = (From tblSanctionList In SanctionListAll.RAW_UN_DOC Select tblSanctionList.SYS_ID).Max

                ' IMPORT Table
                Dim stdDateFmt = "dd/MM/yyyy HH:mm:ss"
                Dim maxImportID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IMPORT_TEMP Select tblSanctionList.IMPORTID).Max
                Dim dbImport18Tbl As New ADOEntitySanctionList.AMLO_NEW_IMPORT_TEMP
                dbImport18Tbl.IMPORTID = maxImportID + 1

                Dim importDate As New Date
                DateTime.TryParseExact(dateUpload, _
                stdDateFmt, New CultureInfo("en-GB"), _
                DateTimeStyles.None, importDate)

                dbImport18Tbl.IMPORT_DATE = importDate

                dbImport18Tbl.FILE_NAME = fileName + "_" + dateUpload
                dbImport18Tbl.FILE_TYPE = fileType
                dbImport18Tbl.IMPORT_BY = userUpload
                SanctionListAll.AddToAMLO_NEW_IMPORT_TEMP(dbImport18Tbl)

                ' Individual
                If Not rawUN.individualsInstance Is Nothing Then
                    Dim listInd As List(Of RawUNXMLEntity.Individual.IndividualDetail) = rawUN.individualsInstance.IndividualListProperty
                    If Not listInd Is Nothing And listInd.Count > 0 Then
                        For Each indEnt As RawUNXMLEntity.Individual.IndividualDetail In listInd
                            If Not indEnt Is Nothing Then
                                dbUNIndividual = New ADOEntitySanctionList.RAW_UN_MASTER
                                db18TblMaster = New ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP

                                dbUNIndividual.DATATID = indEnt.data_id
                                dbUNIndividual.COMMENTS1 = indEnt.comments1

                                dbUNIndividual.FIRST_NAME = indEnt.first_name
                                dbUNIndividual.SECOND_NAME = indEnt.second_name
                                dbUNIndividual.THIRD_NAME = indEnt.third_name
                                dbUNIndividual.FOURTH_NAME = indEnt.fourth_name
                                dbUNIndividual.NAME_ORIGINAL_SCRIPT = indEnt.originalScript
                                dbUNIndividual.NATIONALITY2 = indEnt.nationality2
                                dbUNIndividual.REFERRENCE = indEnt.refference_number
                                dbUNIndividual.SORT_KEY = indEnt.sort_key
                                dbUNIndividual.SORT_KEY_LAST_MOD = indEnt.sort_key_last_modidy
                                dbUNIndividual.UN_LIST_TYPE = indEnt.UN_LIST_TYPE
                                dbUNIndividual.ENTITY_TYPE = indEnt.EntityTypeProperty
                                Dim listOnDate As Date = TryExtractUNDate(indEnt.listOn)
                                dbUNIndividual.LISTED_ON = listOnDate
                                ' Master 
                                db18TblMaster.IMPORTID = dbImport18Tbl.IMPORTID
                                db18TblMaster.ENTITY_ID = mStrSactionType + indEnt.data_id.ToString
                                db18TblMaster.INFO_SOURCE = mStrSactionType
                                db18TblMaster.ENTITY_TYPE = indEnt.EntityTypeProperty
                                db18TblMaster.UN_TYPE = indEnt.UN_LIST_TYPE
                                db18TblMaster.GENDER = indEnt.gender
                                db18TblMaster.PROFILE_NOTES = indEnt.comments1
                                db18TblMaster.RJ_REMARKS = indEnt.refference_number
                                db18TblMaster.ACTION = "Add"
                                db18TblMaster.EXISTING_STATUS = 0
                                Dim sttmDate As Date = TryExtractUNDate(rawUN.unDateGenerate)
                                db18TblMaster.STAMP_DTTM = sttmDate
                                db18TblMaster.LAST_UPDATE_DATE = dateUpload
                                ' Suffix name
                                Dim titleList As New List(Of String)
                                If Not indEnt.title_name Is Nothing Then
                                    If Not indEnt.title_name.values Is Nothing Then
                                        For Each title As String In indEnt.title_name.values
                                            If Not String.IsNullOrEmpty(title) Then
                                                'db18TblName.SUFFIX_E = title
                                                dbUNTitle = New ADOEntitySanctionList.RAW_UN_TITLE
                                                dbUNTitle.DATATID = dbUNIndividual.DATATID
                                                maxTitleUNSYS_ID += 1
                                                dbUNTitle.SYS_ID = maxTitleUNSYS_ID
                                                dbUNTitle.UN_TITLE = title
                                                SanctionListAll.AddToRAW_UN_TITLE(dbUNTitle)

                                                titleList.Add(title)
                                            End If
                                        Next
                                    End If
                                End If

                                'Name
                                Dim nameList As New List(Of String)

                                Dim singleStrName As String = ""
                                If Not String.IsNullOrEmpty(indEnt.first_name) Then
                                    nameList.Add(indEnt.first_name)

                                End If
                                If Not String.IsNullOrEmpty(indEnt.second_name) Then
                                    nameList.Add(indEnt.second_name)

                                End If
                                If Not String.IsNullOrEmpty(indEnt.third_name) Then
                                    nameList.Add(indEnt.third_name)

                                End If
                                If Not String.IsNullOrEmpty(indEnt.fourth_name) Then
                                    nameList.Add(indEnt.fourth_name)

                                End If

                                If nameList.Count = 4 Then
                                    singleStrName = indEnt.first_name + "/" + indEnt.second_name + "/" + indEnt.third_name + "/" + indEnt.fourth_name
                                End If
                                For Each Str As String In nameList
                                    For Each title As String In titleList
                                        db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                                        db18TblName.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        maxName18TblSYS_ID += 1
                                        db18TblName.SYS_ID = maxName18TblSYS_ID
                                        db18TblName.NAME_TYPE = "Primary Name"
                                        db18TblName.ORIGINAL_SCRIPT_NAME = indEnt.originalScript
                                        db18TblName.INFO_SOURCE = mStrSactionType
                                        db18TblName.FIRSTNAME_E = Str
                                        db18TblName.SUFFIX_E = title
                                        db18TblName.SINGLE_STRING_NAME = singleStrName

                                        SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)
                                    Next
                                Next

                                ' Alias name
                                If Not indEnt.aliasList Is Nothing Then
                                    For Each aliasEnt As RawUNXMLEntity.AliasInfo In indEnt.aliasList
                                        dbUNAlias = New ADOEntitySanctionList.RAW_UN_ALIAS
                                        maxAliasUNSYS_ID += 1
                                        dbUNAlias.SYS_ID = maxAliasUNSYS_ID
                                        dbUNAlias.DATATID = dbUNIndividual.DATATID
                                        dbUNAlias.ALIAS_NAME = aliasEnt.alias_name
                                        dbUNAlias.CITY_OF_BIRTH = aliasEnt.cityOfBirth
                                        dbUNAlias.COUNTRY_OF_BIRTH = aliasEnt.countryOFBirth
                                        dbUNAlias.DATE_OF_BIRTH = TryExtractUNDate(aliasEnt.dob)
                                        dbUNAlias.NOTE = aliasEnt.note
                                        dbUNAlias.QUALITY = aliasEnt.quality
                                        SanctionListAll.AddToRAW_UN_ALIAS(dbUNAlias)

                                        db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                                        db18TblName.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        maxName18TblSYS_ID += 1
                                        db18TblName.SYS_ID = maxName18TblSYS_ID
                                        db18TblName.NAME_TYPE = "Also know as"
                                        db18TblName.INFO_SOURCE = mStrSactionType
                                        If Not String.IsNullOrEmpty(aliasEnt.alias_name) Then
                                            If IsSpaceAlphaNumeric(aliasEnt.alias_name) Then
                                                db18TblName.SINGLE_STRING_NAME = aliasEnt.alias_name
                                            Else
                                                db18TblName.ORIGINAL_SCRIPT_NAME = aliasEnt.alias_name
                                            End If
                                        End If
                                        db18TblName.CATEGORY_ = aliasEnt.quality
                                        SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)

                                        ' Temp BirthInfo DB
                                        If Not String.IsNullOrEmpty(aliasEnt.dob) Then
                                            db18TblBirth = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                                            maxDob18TblSYS_ID += 1
                                            db18TblBirth.SYS_ID = maxDob18TblSYS_ID
                                            db18TblBirth.ENTITY_ID = db18TblMaster.ENTITY_ID
                                            db18TblBirth.INFO_TYPE = 1

                                            Dim resDate As Date = TryExtractUNDate(aliasEnt.dob.Replace(" ", "T"))
                                            db18TblBirth.BIRTHINFO_DATE = resDate.Day.ToString + "/" + resDate.Month.ToString + "/" + resDate.Year.ToString
                                            db18TblBirth.MAIN_ENTRY = 1

                                            SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TblBirth)
                                        End If

                                        If Not String.IsNullOrEmpty(aliasEnt.cityOfBirth) Or Not String.IsNullOrEmpty(aliasEnt.countryOFBirth) Then
                                            db18TblBirth = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                                            maxDob18TblSYS_ID += 1
                                            db18TblBirth.SYS_ID = maxDob18TblSYS_ID
                                            db18TblBirth.ENTITY_ID = db18TblMaster.ENTITY_ID
                                            db18TblBirth.INFO_TYPE = 2

                                            Dim strBirthplace As String = ""
                                            If Not String.IsNullOrEmpty(aliasEnt.cityOfBirth) Then
                                                strBirthplace = aliasEnt.cityOfBirth + ","
                                            End If
                                            If Not String.IsNullOrEmpty(aliasEnt.countryOFBirth) Then
                                                db18TblBirth.BIRTHINFO_COUNTRY = aliasEnt.countryOFBirth
                                                strBirthplace += aliasEnt.countryOFBirth
                                            Else
                                                strBirthplace = strBirthplace.Substring(0, strBirthplace.Length - 1)
                                            End If

                                            db18TblBirth.BIRTHINFO_PLACE = strBirthplace

                                            SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TblBirth)
                                        End If

                                    Next
                                End If

                                '18 Tbl and Temp  BirthInfo
                                If Not indEnt.dobList Is Nothing Then
                                    For Each dob As RawUNXMLEntity.BirthInfo In indEnt.dobList
                                        dbUNBirthInfo = New ADOEntitySanctionList.RAW_UN_DOB
                                        maxDOBUNSYS_ID += 1
                                        dbUNBirthInfo.SYS_ID = maxDOBUNSYS_ID
                                        dbUNBirthInfo.DATATID = dbUNIndividual.DATATID
                                        dbUNBirthInfo.BIRTH_DATE = TryExtractUNDate(dob.birthDate)
                                        dbUNBirthInfo.BIRTH_YEAR = dob.year
                                        dbUNBirthInfo.FROM_YEAR = dob.fromYear
                                        dbUNBirthInfo.NOTE = dob.note
                                        dbUNBirthInfo.TO_YEAR = dob.toYear
                                        dbUNBirthInfo.TYPE_OF_DATE = dob.dateType

                                        SanctionListAll.AddToRAW_UN_DOB(dbUNBirthInfo)

                                        db18TblBirth = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                                        maxDob18TblSYS_ID += 1
                                        db18TblBirth.SYS_ID = maxDob18TblSYS_ID
                                        db18TblBirth.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        db18TblBirth.INFO_TYPE = 1
                                        db18TblBirth.MAIN_ENTRY = 1
                                        Dim resDate As Date = TryExtractUNDate(dob.birthDate)
                                        If Not resDate = Nothing Then
                                            db18TblBirth.BIRTHINFO_DATE = resDate.Day.ToString + "/" + resDate.Month.ToString + "/" + resDate.Year.ToString
                                        Else
                                            Dim strDobType As String = dob.dateType
                                            If strDobType Is Nothing Then
                                                strDobType = ""
                                            End If
                                            If Not String.IsNullOrEmpty(strDobType) And strDobType.ToLower.Equals("between") Then
                                                db18TblBirth.RAW_BIRTH_DATE = dob.fromYear + "-" + dob.toYear
                                            Else
                                                If Not String.IsNullOrEmpty(dob.note) Then
                                                    db18TblBirth.RAW_BIRTH_DATE = dob.note
                                                Else
                                                    db18TblBirth.RAW_BIRTH_DATE = dob.year
                                                End If
                                            End If

                                        End If

                                        SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TblBirth)
                                    Next
                                End If

                                '18 Tbl and Raw Place of birth
                                If Not indEnt.pobList Is Nothing Then
                                    For Each pob As RawUNXMLEntity.AddressInfo In indEnt.pobList
                                        dbUNPOBInfo = New ADOEntitySanctionList.RAW_UN_POB
                                        maxPOBUNSYS_ID += 1
                                        dbUNPOBInfo.SYS_ID = maxPOBUNSYS_ID
                                        dbUNPOBInfo.DATATID = dbUNIndividual.DATATID

                                        dbUNPOBInfo.CITY = pob.city
                                        dbUNPOBInfo.COUNTRY = pob.country
                                        dbUNPOBInfo.NOTE = pob.note
                                        dbUNPOBInfo.STATE_PROVINCE = pob.state

                                        SanctionListAll.AddToRAW_UN_POB(dbUNPOBInfo)

                                        db18TblBirth = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
                                        maxDob18TblSYS_ID += 1
                                        db18TblBirth.SYS_ID = maxDob18TblSYS_ID
                                        db18TblBirth.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        db18TblBirth.INFO_TYPE = 2
                                        db18TblBirth.MAIN_ENTRY = 1
                                        Dim sb As New StringBuilder
                                        Dim needToCutComma As Boolean = False
                                        If Not String.IsNullOrEmpty(pob.city) Then
                                            sb.Append(pob.city).Append(",")
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(pob.state) Then
                                            sb.Append(pob.state).Append(",")
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(pob.country) Then
                                            sb.Append(pob.country).Append(",")
                                            db18TblBirth.BIRTHINFO_COUNTRY = pob.country
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(pob.note) Then
                                            sb.Append(pob.note).Append(",")
                                            needToCutComma = True
                                        End If
                                        If needToCutComma Then
                                            sb.Remove(sb.Length - 1, 1)
                                            db18TblBirth.BIRTHINFO_PLACE = sb.ToString
                                        End If
                                        SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TblBirth)
                                    Next
                                End If

                                ' Addresss Temp and 18Tbl
                                If Not indEnt.addressList Is Nothing Then
                                    For Each addr As RawUNXMLEntity.AddressInfo In indEnt.addressList
                                        dbUNAddrinfo = New ADOEntitySanctionList.RAW_UN_ADDRESS
                                        dbUNAddrinfo.CITY = addr.city
                                        dbUNAddrinfo.COUNTRY = addr.country
                                        dbUNAddrinfo.DATATID = dbUNIndividual.DATATID
                                        dbUNAddrinfo.NOTE = addr.note
                                        dbUNAddrinfo.STATE_PROVINCE = addr.state
                                        dbUNAddrinfo.STREET = addr.street
                                        maxAddrUNSYS_ID += 1
                                        dbUNAddrinfo.SYS_ID = maxAddrUNSYS_ID

                                        SanctionListAll.AddToRAW_UN_ADDRESS(dbUNAddrinfo)

                                        db18TblAddress = New ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                                        db18TblAddress.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        maxAddr18TblSYS_ID += 1
                                        db18TblAddress.SYS_ID = maxAddr18TblSYS_ID
                                        Dim sb As New StringBuilder
                                        Dim needToCutComma As Boolean = False
                                        If Not String.IsNullOrEmpty(addr.street) Then
                                            sb.Append(addr.street).Append(",")
                                            db18TblAddress.ADDRESS_STREET = addr.street
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(addr.city) Then
                                            sb.Append(addr.city).Append(",")
                                            db18TblAddress.ADDRESS_CITY = addr.city
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(addr.state) Then
                                            sb.Append(addr.state).Append(",")
                                            db18TblAddress.ADDRESS_STATE = addr.state
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(addr.country) Then
                                            sb.Append(addr.country).Append(",")
                                            db18TblAddress.ADDRESS_COUNTRY = addr.country
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(addr.zipcode) Then
                                            sb.Append(addr.zipcode).Append(",")
                                            db18TblAddress.POST_CODE = addr.zipcode
                                            needToCutComma = True
                                        End If
                                        If Not String.IsNullOrEmpty(addr.note) Then
                                            sb.Append(addr.note).Append(",")
                                            db18TblAddress.ADDRESS_1 = addr.note
                                            If addr.note.Length > maxLenght Then
                                                maxLenght = addr.note.Length
                                            End If
                                            needToCutComma = True
                                        End If
                                        If needToCutComma Then
                                            sb.Remove(sb.Length - 1, 1)
                                            db18TblAddress.ADDRESS_LINE = sb.ToString
                                        End If

                                        SanctionListAll.AddToAMLO_NEW_ADDRESS_TEMP(db18TblAddress)
                                    Next
                                End If

                                ' Nationality temp and Raw
                                If Not String.IsNullOrEmpty(indEnt.nationality2) Then
                                    db18TblResident = New ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                                    db18TblResident.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    maxResident18TblSYS_ID += 1
                                    db18TblResident.SYS_ID = maxResident18TblSYS_ID
                                    db18TblResident.TYPE = 1
                                    db18TblResident.COUNTRY_TYPE = "Nationality"
                                    db18TblResident.COUNTRY_DESC = indEnt.nationality2

                                    SanctionListAll.AddToAMLO_NEW_RESIDENT_TEMP(db18TblResident)
                                End If
                                If indEnt.nationality1 Is Nothing And indEnt.nationality1.values.Count > 0 Then
                                    For Each nation As String In indEnt.nationality1.values
                                        dbUNNationality = New ADOEntitySanctionList.RAW_UN_NATIONALITY
                                        dbUNNationality.DATATID = dbUNIndividual.DATATID
                                        maxNationalityUNSYS_ID += 1
                                        dbUNNationality.SYS_ID = maxNationalityUNSYS_ID

                                        db18TblResident = New ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                                        db18TblResident.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        maxResident18TblSYS_ID += 1
                                        db18TblResident.SYS_ID = maxResident18TblSYS_ID
                                        db18TblResident.TYPE = 1
                                        db18TblResident.COUNTRY_TYPE = "Nationality"
                                        db18TblResident.COUNTRY_DESC = nation

                                        SanctionListAll.AddToAMLO_NEW_RESIDENT_TEMP(db18TblResident)
                                    Next
                                End If

                                'ID number Temp and Raw
                                If Not indEnt.docList Is Nothing Then
                                    For Each doc As RawUNXMLEntity.DocumentInfo In indEnt.docList
                                        dbUNDocument = New ADOEntitySanctionList.RAW_UN_DOC
                                        dbUNDocument.DATATID = dbUNIndividual.DATATID
                                        maxDocUNSYS_ID += 1
                                        dbUNDocument.SYS_ID = maxDocUNSYS_ID

                                        dbUNDocument.COUNTRY_OF_ISSUE = doc.countryIssue
                                        Dim issueDate As Date = Nothing
                                        If Not String.IsNullOrEmpty(doc.issueDate) Then
                                            issueDate = TryExtractUNDate(doc.issueDate.Replace(" ", "T"))
                                            dbUNDocument.DATE_OF_ISSUE = issueDate
                                        End If
                                        dbUNDocument.ID_NUMBER = doc.docNumber
                                        dbUNDocument.ISSUING_COUNTRY = doc.issueCountry
                                        dbUNDocument.NOTE = doc.note
                                        dbUNDocument.TYPE_OF_DOCUMENT = doc.docType1
                                        dbUNDocument.TYPE_OF_DOCUMENT2 = doc.docType2

                                        SanctionListAll.AddToRAW_UN_DOC(dbUNDocument)

                                        db18TblIDNumber = New ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
                                        db18TblIDNumber.ENTITY_ID = db18TblMaster.ENTITY_ID
                                        maxIDNumber18TblSYS_ID += 1
                                        db18TblIDNumber.SYS_ID = maxIDNumber18TblSYS_ID

                                        db18TblIDNumber.ID_TYPE_CODE = 0
                                        Dim typeDoc1 As String = doc.docType1
                                        Dim typeDoc2 As String = doc.docType2

                                        If Not String.IsNullOrEmpty(typeDoc1) Then
                                            If typeDoc1.ToLower.Contains("passport") Then
                                                db18TblIDNumber.ID_TYPE = "Passport"
                                            ElseIf typeDoc1.ToLower.Contains("identification") Then
                                                db18TblIDNumber.ID_TYPE = "National Identification"
                                            Else
                                                db18TblIDNumber.ID_TYPE = "Other"
                                            End If
                                        Else
                                            If String.IsNullOrEmpty(typeDoc2) Then
                                                If typeDoc2.ToLower.Contains("passport") Then
                                                    db18TblIDNumber.ID_TYPE = "Passport"
                                                ElseIf typeDoc2.ToLower.Contains("identification") Then
                                                    db18TblIDNumber.ID_TYPE = "National Identification"
                                                Else
                                                    db18TblIDNumber.ID_TYPE = "Other"
                                                End If
                                            Else
                                                db18TblIDNumber.ID_TYPE = "Other"
                                            End If
                                        End If
                                        db18TblIDNumber.ID_ADDN_REF = doc.note
                                        If Not String.IsNullOrEmpty(doc.docNumber) Then
                                            db18TblIDNumber.ID_VALUE = removeSpecialChar(doc.docNumber)
                                        End If

                                        If Not String.IsNullOrEmpty(doc.countryIssue) Then
                                            db18TblIDNumber.ID_COUNTRY = doc.countryIssue
                                        Else
                                            db18TblIDNumber.ID_COUNTRY = doc.issueCountry
                                        End If

                                        If Not issueDate = Nothing Then
                                            db18TblIDNumber.ID_ISSUEDATE = issueDate.Day.ToString + "/" + issueDate.Month.ToString + "/" + issueDate.Year.ToString
                                        End If

                                        SanctionListAll.AddToAMLO_NEW_IDNUMBER_TEMP(db18TblIDNumber)
                                    Next
                                End If


                                If Not indEnt.listType Is Nothing Then
                                    Dim listTypeVal As List(Of String) = indEnt.listType.values
                                    If Not listTypeVal Is Nothing And listTypeVal.Count > 0 Then
                                        dbUNIndividual.LIST_TYPE = listTypeVal(listTypeVal.Count - 1)
                                    End If

                                End If
                                If Not indEnt.lastdayUpdated Is Nothing Then
                                    Dim lastUpdateVal As List(Of String) = indEnt.lastdayUpdated.values
                                    If Not lastUpdateVal Is Nothing And lastUpdateVal.Count > 0 Then
                                        Dim resDate As Date = TryExtractUNDate(lastUpdateVal(lastUpdateVal.Count - 1))
                                        dbUNIndividual.LAST_DAY_UPDATED = resDate
                                        Dim strDate As String = resDate.Day.ToString + "/" + resDate.Month.ToString + "/" + resDate.Year.ToString
                                        db18TblMaster.LAST_UPDATE_DATE = strDate
                                    End If
                                End If


                                SanctionListAll.AddToRAW_UN_MASTER(dbUNIndividual)
                                SanctionListAll.AddToAMLO_NEW_MASTER_TEMP(db18TblMaster)
                            End If
                        Next
                    End If
                End If
                ' Entity
                If Not rawUN.entitiesInstance Is Nothing Then
                    If Not rawUN.entitiesInstance.EntitieDetails Is Nothing Then
                        For Each entDetail As RawUNXMLEntity.Entities.EntityDetail In rawUN.entitiesInstance.EntitieDetails
                            dbUNIndividual = New ADOEntitySanctionList.RAW_UN_MASTER
                            db18TblMaster = New ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP

                            dbUNIndividual.DATATID = entDetail.data_id
                            dbUNIndividual.COMMENTS1 = entDetail.comments1

                            dbUNIndividual.FIRST_NAME = entDetail.first_name
                            'dbUNIndividual.SECOND_NAME = entDetail.second_name
                            'dbUNIndividual.THIRD_NAME = entDetail.third_name
                            'dbUNIndividual.FOURTH_NAME = entDetail.fourth_name
                            dbUNIndividual.NAME_ORIGINAL_SCRIPT = entDetail.nameOriginalScript
                            'dbUNIndividual.NATIONALITY2 = entDetail.nationality2
                            dbUNIndividual.REFERRENCE = entDetail.refNumber
                            dbUNIndividual.SORT_KEY = entDetail.sortkey
                            dbUNIndividual.SORT_KEY_LAST_MOD = entDetail.sortkeylastmod
                            dbUNIndividual.UN_LIST_TYPE = entDetail.UNListType
                            dbUNIndividual.ENTITY_TYPE = entDetail.EntityTypeProperty
                            Dim listOnDate As Date = TryExtractUNDate(entDetail.listON)
                            dbUNIndividual.LISTED_ON = listOnDate
                            ' Master 
                            db18TblMaster.IMPORTID = dbImport18Tbl.IMPORTID
                            db18TblMaster.ENTITY_ID = mStrSactionType + entDetail.data_id.ToString
                            db18TblMaster.INFO_SOURCE = mStrSactionType
                            db18TblMaster.ENTITY_TYPE = entDetail.EntityTypeProperty
                            db18TblMaster.UN_TYPE = entDetail.UNListType
                            'db18TblMaster.GENDER = entDetail.gender
                            db18TblMaster.PROFILE_NOTES = entDetail.comments1
                            db18TblMaster.RJ_REMARKS = entDetail.refNumber
                            db18TblMaster.ACTION = "Add"
                            db18TblMaster.EXISTING_STATUS = 0
                            db18TblMaster.LAST_UPDATE_DATE = dateUpload

                            Dim sttmDate As Date = TryExtractUNDate(rawUN.unDateGenerate)
                            db18TblMaster.STAMP_DTTM = sttmDate
                            'Name
                            db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                            db18TblName.ENTITY_ID = db18TblMaster.ENTITY_ID
                            maxName18TblSYS_ID += 1
                            db18TblName.SYS_ID = maxName18TblSYS_ID
                            db18TblName.NAME_TYPE = "Primary Name"
                            db18TblName.ORIGINAL_SCRIPT_NAME = entDetail.nameOriginalScript
                            db18TblName.INFO_SOURCE = mStrSactionType

                            If IsSpaceAlphaNumeric(entDetail.first_name) Then
                                db18TblName.ENTITY_NAME_E = entDetail.first_name
                                db18TblName.SINGLE_STRING_NAME = entDetail.first_name
                            Else
                                db18TblName.ENTITY_NAME = entDetail.first_name
                            End If

                            SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)

                            ' Alias name
                            If Not entDetail.aliasList Is Nothing Then
                                For Each aliasEnt As RawUNXMLEntity.AliasInfo In entDetail.aliasList
                                    dbUNAlias = New ADOEntitySanctionList.RAW_UN_ALIAS
                                    maxAliasUNSYS_ID += 1
                                    dbUNAlias.SYS_ID = maxAliasUNSYS_ID
                                    dbUNAlias.DATATID = dbUNIndividual.DATATID
                                    dbUNAlias.ALIAS_NAME = aliasEnt.alias_name
                                    dbUNAlias.CITY_OF_BIRTH = aliasEnt.cityOfBirth
                                    dbUNAlias.COUNTRY_OF_BIRTH = aliasEnt.countryOFBirth
                                    dbUNAlias.DATE_OF_BIRTH = TryExtractUNDate(aliasEnt.dob)
                                    dbUNAlias.NOTE = aliasEnt.note
                                    dbUNAlias.QUALITY = aliasEnt.quality
                                    SanctionListAll.AddToRAW_UN_ALIAS(dbUNAlias)

                                    db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                                    db18TblName.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    maxName18TblSYS_ID += 1
                                    db18TblName.SYS_ID = maxName18TblSYS_ID
                                    db18TblName.NAME_TYPE = "Also know as"
                                    db18TblName.INFO_SOURCE = mStrSactionType
                                    If Not String.IsNullOrEmpty(aliasEnt.alias_name) Then
                                        If IsSpaceAlphaNumeric(aliasEnt.alias_name) Then
                                            db18TblName.SINGLE_STRING_NAME = aliasEnt.alias_name
                                        Else
                                            db18TblName.ORIGINAL_SCRIPT_NAME = aliasEnt.alias_name
                                        End If
                                    End If
                                    db18TblName.CATEGORY_ = aliasEnt.quality
                                    SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)

                                Next
                            End If

                            If Not entDetail.addressList Is Nothing Then
                                For Each addr As RawUNXMLEntity.AddressInfo In entDetail.addressList
                                    dbUNAddrinfo = New ADOEntitySanctionList.RAW_UN_ADDRESS
                                    dbUNAddrinfo.CITY = addr.city
                                    dbUNAddrinfo.COUNTRY = addr.country
                                    dbUNAddrinfo.DATATID = dbUNIndividual.DATATID
                                    dbUNAddrinfo.NOTE = addr.note
                                    dbUNAddrinfo.STATE_PROVINCE = addr.state
                                    dbUNAddrinfo.STREET = addr.street
                                    maxAddrUNSYS_ID += 1
                                    dbUNAddrinfo.SYS_ID = maxAddrUNSYS_ID

                                    SanctionListAll.AddToRAW_UN_ADDRESS(dbUNAddrinfo)

                                    db18TblAddress = New ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
                                    db18TblAddress.ENTITY_ID = db18TblMaster.ENTITY_ID
                                    maxAddr18TblSYS_ID += 1
                                    db18TblAddress.SYS_ID = maxAddr18TblSYS_ID
                                    Dim sb As New StringBuilder
                                    Dim needToCutComma As Boolean = False
                                    If Not String.IsNullOrEmpty(addr.street) Then
                                        sb.Append(addr.street).Append(",")
                                        db18TblAddress.ADDRESS_STREET = addr.street
                                        needToCutComma = True
                                    End If
                                    If Not String.IsNullOrEmpty(addr.city) Then
                                        sb.Append(addr.city).Append(",")
                                        db18TblAddress.ADDRESS_CITY = addr.city
                                        needToCutComma = True
                                    End If
                                    If Not String.IsNullOrEmpty(addr.state) Then
                                        sb.Append(addr.state).Append(",")
                                        db18TblAddress.ADDRESS_STATE = addr.state
                                        needToCutComma = True
                                    End If
                                    If Not String.IsNullOrEmpty(addr.country) Then
                                        sb.Append(addr.country).Append(",")
                                        db18TblAddress.ADDRESS_COUNTRY = addr.country
                                        needToCutComma = True
                                    End If
                                    If Not String.IsNullOrEmpty(addr.zipcode) Then
                                        sb.Append(addr.zipcode).Append(",")
                                        db18TblAddress.POST_CODE = addr.zipcode
                                        needToCutComma = True
                                    End If
                                    If Not String.IsNullOrEmpty(addr.note) Then
                                        sb.Append(addr.note).Append(",")
                                        db18TblAddress.ADDRESS_1 = addr.note
                                        If addr.note.Length > maxLenght Then
                                            maxLenght = addr.note.Length
                                        End If
                                        needToCutComma = True
                                    End If
                                    If needToCutComma Then
                                        sb.Remove(sb.Length - 1, 1)
                                        db18TblAddress.ADDRESS_LINE = sb.ToString
                                    End If

                                    SanctionListAll.AddToAMLO_NEW_ADDRESS_TEMP(db18TblAddress)
                                Next
                            End If

                            SanctionListAll.AddToRAW_UN_MASTER(dbUNIndividual)
                            SanctionListAll.AddToAMLO_NEW_MASTER_TEMP(db18TblMaster)
                        Next
                    End If
                End If
                SanctionListAll.SaveChanges()
            End If


        Catch ex As Exception
            Throw New Exception("เกิดข้อผิดพลาดในการแปลงไฟล์" & ex.InnerException.Message.ToString)
        End Try
        Return mStrSactionType
    End Function
#End Region

    Public Function addToAMLAndTemp(ByVal ds As Data.DataSet, ByRef fileName As String, ByRef fileType As String, ByRef dateUpload As String, ByRef userUpload As String) As String
        Dim mStrSactionType = ""
        Try
            ' TODO delete all data before insert
            Dim dbMgr As New clsDatabase
            dbMgr.TransStart()
            Dim dummy As New Collection

            dbMgr.TransExecute("delete from KYCADMIN.amlo_address", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_birthinfo", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_classification", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_criminal", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_description", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_education", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_falsepositive", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_idnumber", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_image", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_name", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_relationship", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_resident", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_sanction", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_role", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_source", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_str", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_vessel", dummy)
            dbMgr.TransExecute("delete from KYCADMIN.amlo_master", dummy)


            dbMgr.TransCommit()
            ' end delete

            ' Setup Variable
            ' Raw Table
            Dim amloMaster As ADOEntitySanctionList.AMLO_MASTER
            Dim amloAddress As ADOEntitySanctionList.AMLO_ADDRESS
            Dim amloBirth As ADOEntitySanctionList.AMLO_BIRTHINFO
            Dim amloClassification As ADOEntitySanctionList.AMLO_CLASSIFICATION
            Dim amloCriminal As ADOEntitySanctionList.AMLO_CRIMINAL
            Dim amloDesc As ADOEntitySanctionList.AMLO_DESCRIPTION
            Dim amloEdu As ADOEntitySanctionList.AMLO_EDUCATION
            Dim amloFalse As ADOEntitySanctionList.AMLO_FALSEPOSITIVE
            Dim amloIDNum As ADOEntitySanctionList.AMLO_IDNUMBER
            Dim amloImg As ADOEntitySanctionList.AMLO_IMAGE
            Dim amloName As ADOEntitySanctionList.AMLO_NAME
            Dim amloRelation As ADOEntitySanctionList.AMLO_RELATIONSHIP
            Dim amloResident As ADOEntitySanctionList.AMLO_RESIDENT
            Dim amloRole As ADOEntitySanctionList.AMLO_ROLE
            Dim amloSanction As ADOEntitySanctionList.AMLO_SANCTION
            Dim amloSource As ADOEntitySanctionList.AMLO_SOURCE
            Dim amloStr As ADOEntitySanctionList.AMLO_STR
            Dim amloVessel As ADOEntitySanctionList.AMLO_VESSEL


            ' Temp Table
            Dim db18TblMaster As ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
            Dim db18TblAddress As ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP
            Dim db18TblBirth As ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP
            Dim db18TblClassification As ADOEntitySanctionList.AMLO_NEW_CLASSIFICATION_TEMP
            Dim db18TblCriminal As ADOEntitySanctionList.AMLO_NEW_CRIMINAL_TEMP
            Dim db18TblDesc As ADOEntitySanctionList.AMLO_NEW_DESCRIPTION_TEMP
            Dim db18TblEdu As ADOEntitySanctionList.AMLO_NEW_EDUCATION_TEMP
            Dim db18TblFalse As ADOEntitySanctionList.AMLO_NEW_FALSEPOSITIVE_TEMP
            Dim db18TblIDNum As ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
            Dim db18TblImage As ADOEntitySanctionList.AMLO_NEW_IMAGE_TEMP
            Dim db18TblName As ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
            Dim db18TblRelation As ADOEntitySanctionList.AMLO_NEW_RELATIONSHIP_TEMP
            Dim db18TblResident As ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
            Dim db18TblRole As ADOEntitySanctionList.AMLO_NEW_ROLE_TEMP
            Dim db18TblSanction As ADOEntitySanctionList.AMLO_NEW_SANCTION_TEMP
            Dim db18TblSource As ADOEntitySanctionList.AMLO_NEW_SOURCE_TEMP
            Dim db18TblStr As ADOEntitySanctionList.AMLO_NEW_STR_TEMP
            Dim db18TblVessel As ADOEntitySanctionList.AMLO_NEW_VESSEL_TEMP

            ' Index SYS_ID
            ' ==Max 18tbl address index
            Dim maxAddr18TblSYS_ID As Integer = -1
            maxAddr18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_ADDRESS_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tbl birth index
            Dim maxBirth18TblSYS_ID As Integer = -1
            maxBirth18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_BIRTHINFO_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tbl classification index
            Dim maxClassification18TblSYS_ID As Integer = -1
            maxClassification18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_CLASSIFICATION_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblcriminal index
            Dim maxCriminal18TblSYS_ID As Integer = -1
            maxCriminal18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_CRIMINAL_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblDescription index
            Dim maxDesc18TblSYS_ID As Integer = -1
            maxDesc18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_DESCRIPTION_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblEducation index
            Dim maxEdu18TblSYS_ID As Integer = -1
            maxEdu18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_EDUCATION_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblIDNumber index
            Dim maxIDNum18TblSYS_ID As Integer = -1
            maxIDNum18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IDNUMBER_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblImage index
            Dim maxImage18TblSYS_ID As Integer = -1
            maxImage18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IMAGE_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblName index
            Dim maxName18TblSYS_ID As Integer = -1
            maxName18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_NAME_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblRelation index
            Dim maxRelation18TblSYS_ID As Integer = -1
            maxRelation18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_RELATIONSHIP_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblRelation index
            Dim maxResident18TblSYS_ID As Integer = -1
            maxResident18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_RESIDENT_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblRole index
            Dim maxRole18TblSYS_ID As Integer = -1
            maxRole18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_ROLE_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblSanction index
            Dim maxSanction18TblSYS_ID As Integer = -1
            maxSanction18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_SANCTION_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblSource index
            Dim maxSource18TblSYS_ID As Integer = -1
            maxSource18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_SOURCE_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblStr index
            Dim maxStr18TblSYS_ID As Integer = -1
            maxStr18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_STR_TEMP Select tblSanctionList.SYS_ID).Max
            ' ==Max 18tblVessel index
            Dim maxVessel18TblSYS_ID As Integer = -1
            maxVessel18TblSYS_ID = (From tblSanctionList In SanctionListAll.AMLO_NEW_VESSEL_TEMP Select tblSanctionList.SYS_ID).Max

            ' IMPORT Table
            Dim stdDateFmt = "dd/MM/yyyy HH:mm:ss"
            Dim maxImportID = (From tblSanctionList In SanctionListAll.AMLO_NEW_IMPORT_TEMP Select tblSanctionList.IMPORTID).Max
            Dim dbImport18Tbl As New ADOEntitySanctionList.AMLO_NEW_IMPORT_TEMP
            dbImport18Tbl.IMPORTID = maxImportID + 1

            Dim importDate As New Date
            DateTime.TryParseExact(dateUpload, _
            stdDateFmt, CultureInfo.InvariantCulture, _
            DateTimeStyles.None, importDate)
            dbImport18Tbl.IMPORT_DATE = importDate

            dbImport18Tbl.FILE_NAME = fileName + "_" + dateUpload
            dbImport18Tbl.FILE_TYPE = fileType
            dbImport18Tbl.IMPORT_BY = userUpload
            SanctionListAll.AddToAMLO_NEW_IMPORT_TEMP(dbImport18Tbl)

            Dim dtMaster = ds.Tables("master")

            If Not dtMaster Is Nothing Then
                If dtMaster.Rows.Count > 0 Then
                    For i As Integer = 0 To dtMaster.Rows.Count - 1
                        Dim row As DataRow = dtMaster.Rows(i)
                        If Not String.IsNullOrEmpty(row(0).ToString) Then
                            ' Master Raw
                            amloMaster = New ADOEntitySanctionList.AMLO_MASTER
                            amloMaster.ENTITY_ID = row(0)
                            amloMaster.INFO_SOURCE = row(1).ToString
                            mStrSactionType = amloMaster.INFO_SOURCE
                            amloMaster.ENTITY_TYPE = row(2).ToString
                            amloMaster.BRIEF_NAME = row(3).ToString
                            amloMaster.GENDER = row(4).ToString
                            amloMaster.EXISTING_STATUS = GetValueOrBlankStringRowData(row, 5)
                            amloMaster.BIRTH_PLACE = row(6).ToString
                            amloMaster.PROFILE_NOTES = row(7).ToString
                            amloMaster.DATE_OF_BIRTH = row(8).ToString
                            amloMaster.DATE_OF_DEATH = row(9).ToString
                            amloMaster.ACTIVE_STATUS = row(10).ToString
                            If Not row(11) Is DBNull.Value Then
                                amloMaster.COMP_CODE = GetValueOrBlankStringRowData(row, 11)
                            End If

                            amloMaster.DECEASED = row(12).ToString
                            amloMaster.HIGH_RISK_COUNTRY = row(13).ToString
                            amloMaster.HIGH_RISK_COUNTRY_SCORE = GetValueOrBlankStringRowData(row, 14)
                            amloMaster.FALSE_POSITIVE = GetValueOrBlankStringRowData(row, 15)
                            amloMaster.FALSE_POSITIVE_EXPIRY_DT = row(16).ToString
                            amloMaster.BATCH_NO = row(17).ToString
                            amloMaster.BATCH_DATE = row(18).ToString
                            amloMaster.APPROVAL_STATUS = GetValueOrBlankStringRowData(row, 19)
                            amloMaster.RJ_REMARKS = row(20).ToString
                            amloMaster.APPROVED_BY = row(21).ToString
                            amloMaster.APPROVED_DT = row(22).ToString
                            amloMaster.APPROVED_TM = GetValueOrBlankStringRowData(row, 23)
                            amloMaster.ACTION = row(24).ToString
                            amloMaster.LAST_UPDATE_DATE = row(25).ToString
                            amloMaster.STAMP_DTTM = row(26).ToString
                            amloMaster.USER_ID = row(27).ToString
                            amloMaster.MODE_MN = row(28).ToString
                            SanctionListAll.AddToAMLO_MASTER(amloMaster)

                            ' Master Temp
                            db18TblMaster = New ADOEntitySanctionList.AMLO_NEW_MASTER_TEMP
                            db18TblMaster.ENTITY_ID = row(0)
                            db18TblMaster.IMPORTID = dbImport18Tbl.IMPORTID
                            db18TblMaster.INFO_SOURCE = row(1).ToString
                            db18TblMaster.ENTITY_TYPE = row(2).ToString
                            db18TblMaster.BRIEF_NAME = row(3).ToString
                            db18TblMaster.GENDER = row(4).ToString
                            db18TblMaster.EXISTING_STATUS = GetValueOrBlankStringRowData(row, 5)
                            db18TblMaster.BIRTH_PLACE = row(6).ToString
                            db18TblMaster.PROFILE_NOTES = row(7).ToString
                            db18TblMaster.DATE_OF_BIRTH = row(8).ToString
                            db18TblMaster.DATE_OF_DEATH = row(9).ToString
                            db18TblMaster.ACTIVE_STATUS = row(10).ToString
                            db18TblMaster.COMP_CODE = amloMaster.COMP_CODE
                            db18TblMaster.DECEASED = row(12).ToString
                            db18TblMaster.HIGH_RISK_COUNTRY = row(13).ToString
                            db18TblMaster.HIGH_RISK_COUNTRY_SCORE = amloMaster.HIGH_RISK_COUNTRY_SCORE
                            db18TblMaster.FALSE_POSITIVE = amloMaster.FALSE_POSITIVE
                            db18TblMaster.FALSE_POSITIVE_EXPIRY_DT = row(16).ToString
                            db18TblMaster.BATCH_NO = row(17).ToString
                            db18TblMaster.BATCH_DATE = row(18).ToString
                            db18TblMaster.APPROVAL_STATUS = amloMaster.APPROVAL_STATUS
                            db18TblMaster.RJ_REMARKS = row(20).ToString
                            db18TblMaster.APPROVED_BY = row(21).ToString
                            db18TblMaster.APPROVED_DT = row(22).ToString
                            db18TblMaster.APPROVED_TM = amloMaster.APPROVED_TM
                            db18TblMaster.ACTION = row(24).ToString
                            db18TblMaster.LAST_UPDATE_DATE = dateUpload 'row(25).ToString

                            Dim rawDate = row(26).ToString
                            If Not String.IsNullOrEmpty(rawDate) Then
                                rawDate = rawDate.Split(" ")(0)

                                Dim resDate As New Date
                                DateTime.TryParseExact(rawDate, _
               {"dd/MM/yyyy", "yyyyMMdd"}, CultureInfo.InvariantCulture, _
               DateTimeStyles.None, resDate)
                                db18TblMaster.STAMP_DTTM = resDate
                            End If


                            db18TblMaster.USER_ID = row(27).ToString
                            db18TblMaster.MODE_MN = row(28).ToString
                            SanctionListAll.AddToAMLO_NEW_MASTER_TEMP(db18TblMaster)

                        End If
                    Next


                    Dim dtAddress = ds.Tables("address")
                    If Not dtAddress Is Nothing Then
                        If dtAddress.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtAddress.Rows.Count - 1)
                                Dim row As DataRow = dtAddress.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    maxAddr18TblSYS_ID += 1
                                    amloAddress = New ADOEntitySanctionList.AMLO_ADDRESS
                                    amloAddress.ENTITY_ID = row(0)

                                    amloAddress.SYS_ID = maxAddr18TblSYS_ID
                                    amloAddress.ADDRESS_LINE = row(2).ToString
                                    amloAddress.ADDRESS_CITY = row(3).ToString
                                    amloAddress.ADDRESS_STATE = row(4).ToString
                                    amloAddress.ADDRESS_COUNTRY = row(5).ToString
                                    amloAddress.SUB_ENTITY_ID = row(6).ToString
                                    amloAddress.BATCH_NO = row(7).ToString
                                    amloAddress.BATCH_DATE = row(8).ToString
                                    SanctionListAll.AddToAMLO_ADDRESS(amloAddress)

                                    db18TblAddress = New ADOEntitySanctionList.AMLO_NEW_ADDRESS_TEMP

                                    db18TblAddress.SYS_ID = maxAddr18TblSYS_ID
                                    db18TblAddress.ENTITY_ID = row(0)
                                    db18TblAddress.ADDRESS_LINE = row(2).ToString
                                    db18TblAddress.ADDRESS_CITY = row(3).ToString
                                    db18TblAddress.ADDRESS_STATE = row(4).ToString
                                    db18TblAddress.ADDRESS_COUNTRY = row(5).ToString
                                    db18TblAddress.SUB_ENTITY_ID = row(6).ToString
                                    db18TblAddress.BATCH_NO = row(7).ToString
                                    db18TblAddress.BATCH_DATE = row(8).ToString
                                    SanctionListAll.AddToAMLO_NEW_ADDRESS_TEMP(db18TblAddress)

                                End If
                            Next
                        End If
                    End If

                    Dim dtBirth = ds.Tables("birth")
                    If Not dtBirth Is Nothing Then
                        If dtBirth.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtBirth.Rows.Count - 1)
                                Dim row As DataRow = dtBirth.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    maxBirth18TblSYS_ID += 1

                                    amloBirth = New ADOEntitySanctionList.AMLO_BIRTHINFO
                                    amloBirth.ENTITY_ID = row(0).ToString()
                                    amloBirth.SYS_ID = maxBirth18TblSYS_ID
                                    amloBirth.INFO_TYPE = row(2).ToString()
                                    amloBirth.BIRTHINFO_DATE = row(3).ToString()
                                    amloBirth.BIRTHINFO_PLACE = row(4).ToString()
                                    amloBirth.BIRTHINFO_COUNTRY = row(5).ToString()
                                    amloBirth.MAIN_ENTRY = GetValueOrBlankStringRowData(row, 6)
                                    amloBirth.SUB_ENTITY_ID = row(7).ToString()
                                    amloBirth.BATCH_NO = row(8).ToString()
                                    amloBirth.BATCH_DATE = row(9).ToString()
                                    SanctionListAll.AddToAMLO_BIRTHINFO(amloBirth)

                                    db18TblBirth = New ADOEntitySanctionList.AMLO_NEW_BIRTHINFO_TEMP

                                    db18TblBirth.SYS_ID = maxBirth18TblSYS_ID
                                    db18TblBirth.ENTITY_ID = row(0).ToString()
                                    db18TblBirth.INFO_TYPE = row(2).ToString()
                                    db18TblBirth.BIRTHINFO_DATE = row(3).ToString()
                                    db18TblBirth.RAW_BIRTH_DATE = row(4).ToString()
                                    db18TblBirth.BIRTHINFO_COUNTRY = row(5).ToString()
                                    db18TblBirth.MAIN_ENTRY = amloBirth.MAIN_ENTRY
                                    db18TblBirth.SUB_ENTITY_ID = row(7).ToString()
                                    db18TblBirth.BATCH_NO = row(8).ToString()
                                    db18TblBirth.BATCH_DATE = row(9).ToString()
                                    SanctionListAll.AddToAMLO_NEW_BIRTHINFO_TEMP(db18TblBirth)
                                End If
                            Next
                        End If
                    End If

                    Dim dtClassi = ds.Tables("classification")
                    If Not dtClassi Is Nothing Then
                        If dtClassi.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtClassi.Rows.Count - 1)
                                Dim row As DataRow = dtClassi.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    maxClassification18TblSYS_ID += 1
                                    db18TblClassification = New ADOEntitySanctionList.AMLO_NEW_CLASSIFICATION_TEMP
                                    amloClassification = New ADOEntitySanctionList.AMLO_CLASSIFICATION

                                    amloClassification.ENTITY_ID = row(0).ToString()
                                    amloClassification.SYS_ID = maxClassification18TblSYS_ID
                                    amloClassification.REGULATION_DESC = row(2).ToString()
                                    amloClassification.SECTION_NO = GetValueOrBlankStringRowData(row, 3)
                                    amloClassification.SUB_SECTION_NO = GetValueOrBlankStringRowData(row, 4)
                                    amloClassification.SUBSUB_SECTION_NO = GetValueOrBlankStringRowData(row, 5)
                                    amloClassification.REGULATION_MAIN = row(6).ToString()
                                    amloClassification.REGULATION_SUB = row(7).ToString()
                                    amloClassification.REGULATION_SUBSUB = row(8).ToString()

                                    db18TblClassification.ENTITY_ID = row(0).ToString()

                                    db18TblClassification.SYS_ID = maxClassification18TblSYS_ID
                                    db18TblClassification.REGULATION_DESC = row(2).ToString()
                                    db18TblClassification.SECTION_NO = amloClassification.SECTION_NO
                                    db18TblClassification.SUB_SECTION_NO = amloClassification.SUB_SECTION_NO
                                    db18TblClassification.SUBSUB_SECTION_NO = amloClassification.SUBSUB_SECTION_NO
                                    db18TblClassification.REGULATION_MAIN = row(6).ToString()
                                    db18TblClassification.REGULATION_SUB = row(7).ToString()
                                    db18TblClassification.REGULATION_SUBSUB = row(8).ToString()

                                    amloClassification.USER_ID = row(10).ToString()
                                    amloClassification.MODE_MN = row(11).ToString()
                                    If Not String.IsNullOrEmpty(row(9).ToString()) Then
                                        Dim resDate = New Date
                                        Dim resParse = DateTime.TryParseExact(row(9).ToString, _
                                        {"dd/M/yyyy h:m:ss tt", "d-MMM-yy h:m:ss tt"}, CultureInfo.InvariantCulture, _
                                           DateTimeStyles.None, resDate)
                                        amloClassification.STAMP_DTTM = resDate 'ex. "23-Apr-13 5:11:00 AM"
                                        db18TblClassification.STAMP_DTTM = resDate
                                    End If

                                    SanctionListAll.AddToAMLO_CLASSIFICATION(amloClassification)
                                    SanctionListAll.AddToAMLO_NEW_CLASSIFICATION_TEMP(db18TblClassification)
                                End If
                            Next
                        End If
                    End If

                    Dim dtCriminal = ds.Tables("criminal")
                    If Not dtCriminal Is Nothing Then
                        If dtCriminal.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtCriminal.Rows.Count - 1)
                                Dim row As DataRow = dtCriminal.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblCriminal = New ADOEntitySanctionList.AMLO_NEW_CRIMINAL_TEMP
                                    amloCriminal = New ADOEntitySanctionList.AMLO_CRIMINAL

                                    maxCriminal18TblSYS_ID += 1

                                    amloCriminal.ENTITY_ID = row(0).ToString()
                                    amloCriminal.SYS_ID = maxCriminal18TblSYS_ID
                                    amloCriminal.SINCE_DAY = GetValueOrBlankStringRowData(row, 2)
                                    amloCriminal.SINCE_MONTH = GetValueOrBlankStringRowData(row, 3)
                                    amloCriminal.SINCE_YEAR = GetValueOrBlankStringRowData(row, 4)
                                    amloCriminal.TO_DAY = GetValueOrBlankStringRowData(row, 5)
                                    amloCriminal.TO_MONTH = GetValueOrBlankStringRowData(row, 6)
                                    amloCriminal.TO_YEAR = GetValueOrBlankStringRowData(row, 7)
                                    amloCriminal.REFERENCE = row(8).ToString()
                                    amloCriminal.BATCH_NO = row(9).ToString()
                                    amloCriminal.BATCH_DATE = row(10).ToString()
                                    SanctionListAll.AddToAMLO_CRIMINAL(amloCriminal)

                                    db18TblCriminal.ENTITY_ID = row(0).ToString()

                                    db18TblCriminal.SYS_ID = maxCriminal18TblSYS_ID
                                    db18TblCriminal.SINCE_DAY = amloCriminal.SINCE_DAY
                                    db18TblCriminal.SINCE_MONTH = amloCriminal.SINCE_MONTH
                                    db18TblCriminal.SINCE_YEAR = amloCriminal.SINCE_YEAR
                                    db18TblCriminal.TO_DAY = amloCriminal.TO_DAY
                                    db18TblCriminal.TO_MONTH = amloCriminal.TO_MONTH
                                    db18TblCriminal.TO_YEAR = amloCriminal.TO_YEAR
                                    db18TblCriminal.REFERENCE = row(8).ToString()
                                    db18TblCriminal.BATCH_NO = row(9).ToString()
                                    db18TblCriminal.BATCH_DATE = row(10).ToString()
                                    SanctionListAll.AddToAMLO_NEW_CRIMINAL_TEMP(db18TblCriminal)
                                End If
                            Next
                        End If
                    End If

                    Dim dtDesc = ds.Tables("description")
                    If Not dtDesc Is Nothing Then
                        If dtDesc.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtDesc.Rows.Count - 1)
                                Dim row As DataRow = dtDesc.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblDesc = New ADOEntitySanctionList.AMLO_NEW_DESCRIPTION_TEMP
                                    amloDesc = New ADOEntitySanctionList.AMLO_DESCRIPTION

                                    maxDesc18TblSYS_ID += 1

                                    amloDesc.ENTITY_ID = row(0).ToString()
                                    amloDesc.SYS_ID = maxDesc18TblSYS_ID
                                    amloDesc.DESCRIPTION = row(2).ToString()
                                    amloDesc.BATCH_NO = row(3).ToString()
                                    amloDesc.BATCH_DATE = row(4).ToString()

                                    db18TblDesc.ENTITY_ID = row(0).ToString()

                                    db18TblDesc.SYS_ID = maxDesc18TblSYS_ID
                                    db18TblDesc.DESCRIPTION = row(2).ToString()
                                    db18TblDesc.BATCH_NO = row(3).ToString()
                                    db18TblDesc.BATCH_DATE = row(4).ToString()

                                    SanctionListAll.AddToAMLO_DESCRIPTION(amloDesc)
                                    SanctionListAll.AddToAMLO_NEW_DESCRIPTION_TEMP(db18TblDesc)
                                End If
                            Next
                        End If
                    End If

                    Dim dtEdu = ds.Tables("education")
                    If Not dtEdu Is Nothing Then
                        If dtEdu.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtEdu.Rows.Count - 1)
                                Dim row As DataRow = dtEdu.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblEdu = New ADOEntitySanctionList.AMLO_NEW_EDUCATION_TEMP
                                    amloEdu = New ADOEntitySanctionList.AMLO_EDUCATION

                                    maxEdu18TblSYS_ID += 1

                                    amloEdu.ENTITY_ID = row(0).ToString()
                                    amloEdu.SYS_ID = maxEdu18TblSYS_ID
                                    amloEdu.SINCE_DAY = GetValueOrBlankStringRowData(row, 2)
                                    amloEdu.SINCE_MONTH = GetValueOrBlankStringRowData(row, 3)
                                    amloEdu.SINCE_YEAR = GetValueOrBlankStringRowData(row, 4)
                                    amloEdu.TO_DAY = GetValueOrBlankStringRowData(row, 5)
                                    amloEdu.TO_MONTH = GetValueOrBlankStringRowData(row, 6)
                                    amloEdu.TO_YEAR = GetValueOrBlankStringRowData(row, 7)
                                    amloEdu.INSTITUTE_NAME = row(8).ToString()
                                    amloEdu.QUALIFICATION = row(9).ToString()
                                    amloEdu.MAJOR_SUBJECT = row(10).ToString()
                                    amloEdu.BATCH_NO = row(11).ToString()
                                    amloEdu.BATCH_DATE = row(12).ToString()
                                    amloEdu.USER_ID = row(13).ToString()
                                    amloEdu.MODE_MN = row(15).ToString()

                                    db18TblEdu.ENTITY_ID = row(0).ToString()

                                    db18TblEdu.SYS_ID = maxEdu18TblSYS_ID
                                    db18TblEdu.SINCE_DAY = amloEdu.SINCE_DAY
                                    db18TblEdu.SINCE_MONTH = amloEdu.SINCE_MONTH
                                    db18TblEdu.SINCE_YEAR = amloEdu.SINCE_YEAR
                                    db18TblEdu.TO_DAY = amloEdu.TO_DAY
                                    db18TblEdu.TO_MONTH = amloEdu.TO_MONTH
                                    db18TblEdu.TO_YEAR = amloEdu.TO_YEAR
                                    db18TblEdu.INSTITUTE_NAME = row(8).ToString()
                                    db18TblEdu.QUALIFICATION = row(9).ToString()
                                    db18TblEdu.MAJOR_SUBJECT = row(10).ToString()
                                    db18TblEdu.BATCH_NO = row(11).ToString()
                                    db18TblEdu.BATCH_DATE = row(12).ToString()
                                    db18TblEdu.USER_ID = row(13).ToString()
                                    db18TblEdu.MODE_MN = row(15).ToString()

                                    If Not String.IsNullOrEmpty(row(14).ToString()) Then
                                        Dim resDate = New Date
                                        Dim resParse = DateTime.TryParseExact(row(14).ToString, _
                                        {"dd/M/yyyy h:m:ss tt", "d-MMM-yy h:m:ss tt"}, CultureInfo.InvariantCulture, _
                                           DateTimeStyles.None, resDate)
                                        amloEdu.STAMP_DTTM = resDate 'ex. "23-Apr-13 5:11:00 AM"
                                        db18TblEdu.STAMP_DTTM = resDate
                                    End If

                                    SanctionListAll.AddToAMLO_EDUCATION(amloEdu)
                                    SanctionListAll.AddToAMLO_NEW_EDUCATION_TEMP(db18TblEdu)
                                End If
                            Next
                        End If
                    End If

                    Dim dtFalse = ds.Tables("falsepositive")
                    If Not dtFalse Is Nothing Then
                        If dtFalse.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtFalse.Rows.Count - 1)
                                Dim row As DataRow = dtFalse.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblFalse = New ADOEntitySanctionList.AMLO_NEW_FALSEPOSITIVE_TEMP
                                    amloFalse = New ADOEntitySanctionList.AMLO_FALSEPOSITIVE

                                    amloFalse.ENTITY_ID = row(0).ToString()
                                    amloFalse.FALSE_POSITIVE = GetValueOrBlankStringRowData(row, 1)
                                    amloFalse.BATCH_NO = row(3).ToString()
                                    amloFalse.BATCH_DATE = row(4).ToString()

                                    db18TblFalse.ENTITY_ID = row(0).ToString()
                                    db18TblFalse.FALSE_POSITIVE = amloFalse.FALSE_POSITIVE
                                    db18TblFalse.BATCH_NO = row(3).ToString()
                                    db18TblFalse.BATCH_DATE = row(4).ToString()

                                    If Not String.IsNullOrEmpty(row(2).ToString()) Then
                                        Dim resDate = New Date
                                        Dim resParse = DateTime.TryParseExact(row(2).ToString, _
                                        {"dd/M/yyyy h:m:ss tt", "d-MMM-yy h:m:ss tt"}, CultureInfo.InvariantCulture, _
                                           DateTimeStyles.None, resDate)
                                        amloFalse.FALSE_POSITIVE_EXPIRY_DT = resDate 'ex. "23-Apr-13 5:11:00 AM"
                                        db18TblFalse.FALSE_POSITIVE_EXPIRY_DT = resDate
                                    End If


                                    SanctionListAll.AddToAMLO_FALSEPOSITIVE(amloFalse)
                                    SanctionListAll.AddToAMLO_NEW_FALSEPOSITIVE_TEMP(db18TblFalse)
                                End If
                            Next
                        End If
                    End If

                    Dim dtIDNum = ds.Tables("idnumber")
                    If Not dtIDNum Is Nothing Then
                        If dtIDNum.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtIDNum.Rows.Count - 1)
                                Dim row As DataRow = dtIDNum.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblIDNum = New ADOEntitySanctionList.AMLO_NEW_IDNUMBER_TEMP
                                    amloIDNum = New ADOEntitySanctionList.AMLO_IDNUMBER

                                    db18TblIDNum.ENTITY_ID = row(0).ToString
                                    maxIDNum18TblSYS_ID += 1
                                    db18TblIDNum.SYS_ID = maxIDNum18TblSYS_ID
                                    db18TblIDNum.ID_TYPE_CODE = GetValueOrBlankStringRowData(row, 2)
                                    db18TblIDNum.ID_TYPE = row(3).ToString
                                    db18TblIDNum.ID_VALUE = row(4).ToString
                                    db18TblIDNum.ID_COUNTRY = row(5).ToString
                                    db18TblIDNum.ID_ISSUEDATE = row(6).ToString
                                    db18TblIDNum.ID_EXPIRYDATE = row(7).ToString
                                    db18TblIDNum.ID_ADDN_REF = row(8).ToString
                                    db18TblIDNum.SUB_ENTITY_ID = row(9).ToString
                                    db18TblIDNum.INFO_SOURCE = row(10).ToString
                                    db18TblIDNum.ORG_ID = row(11).ToString
                                    db18TblIDNum.BATCH_NO = row(12).ToString
                                    db18TblIDNum.BATCH_DATE = row(13).ToString

                                    amloIDNum.ENTITY_ID = row(0).ToString
                                    amloIDNum.SYS_ID = maxIDNum18TblSYS_ID
                                    amloIDNum.ID_TYPE_CODE = db18TblIDNum.ID_TYPE_CODE
                                    amloIDNum.ID_TYPE = row(3).ToString
                                    amloIDNum.ID_VALUE = row(4).ToString
                                    amloIDNum.ID_COUNTRY = row(5).ToString
                                    amloIDNum.ID_ISSUEDATE = row(6).ToString
                                    amloIDNum.ID_EXPIRYDATE = row(7).ToString
                                    amloIDNum.ID_ADDN_REF = row(8).ToString
                                    amloIDNum.SUB_ENTITY_ID = row(9).ToString
                                    amloIDNum.INFO_SOURCE = row(10).ToString
                                    amloIDNum.ORG_ID = row(11).ToString
                                    amloIDNum.BATCH_NO = row(12).ToString
                                    amloIDNum.BATCH_DATE = row(13).ToString

                                    SanctionListAll.AddToAMLO_IDNUMBER(amloIDNum)
                                    SanctionListAll.AddToAMLO_NEW_IDNUMBER_TEMP(db18TblIDNum)
                                End If
                            Next
                        End If
                    End If

                    Dim dtImage = ds.Tables("image")
                    If Not dtImage Is Nothing Then
                        If dtImage.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtImage.Rows.Count - 1)
                                Dim row As DataRow = dtImage.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblImage = New ADOEntitySanctionList.AMLO_NEW_IMAGE_TEMP
                                    amloImg = New ADOEntitySanctionList.AMLO_IMAGE

                                    maxImage18TblSYS_ID += 1

                                    amloImg.ENTITY_ID = row(0).ToString
                                    amloImg.SYS_ID = maxImage18TblSYS_ID
                                    amloImg.IMAGE_URL = row(2).ToString
                                    amloImg.BATCH_NO = row(3).ToString
                                    amloImg.BATCH_DATE = row(4).ToString

                                    db18TblImage.ENTITY_ID = row(0).ToString

                                    db18TblImage.SYS_ID = maxImage18TblSYS_ID
                                    db18TblImage.IMAGE_URL = row(2).ToString
                                    db18TblImage.BATCH_NO = row(3).ToString
                                    db18TblImage.BATCH_DATE = row(4).ToString

                                    SanctionListAll.AddToAMLO_IMAGE(amloImg)
                                    SanctionListAll.AddToAMLO_NEW_IMAGE_TEMP(db18TblImage)
                                End If
                            Next
                        End If
                    End If

                    Dim dtName = ds.Tables("name")
                    If Not dtName Is Nothing Then
                        If dtName.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtName.Rows.Count - 1)
                                Dim row As DataRow = dtName.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblName = New ADOEntitySanctionList.AMLO_NEW_NAME_TEMP
                                    amloName = New ADOEntitySanctionList.AMLO_NAME


                                    Dim firstName = row(3).ToString
                                    Dim middleName = row(4).ToString
                                    Dim surName = row(5).ToString
                                    Dim entityName = row(7).ToString
                                    Dim suffix = row(6).ToString
                                    maxName18TblSYS_ID += 1

                                    amloName.ENTITY_ID = row(0).ToString
                                    amloName.SYS_ID = maxName18TblSYS_ID
                                    amloName.NAME_TYPE = row(2).ToString
                                    amloName.FIRST_NAME = firstName
                                    amloName.MIDDLE_NAME = middleName
                                    amloName.SURNAME = surName
                                    amloName.SUFFIX = suffix
                                    amloName.ENTITY_NAME = entityName
                                    amloName.ENGLISH_NAME = row(8).ToString
                                    amloName.SINGLE_STRING_NAME = row(9).ToString
                                    amloName.ORIGINAL_SCRIPT_NAME = row(10).ToString
                                    amloName.FALSE_POSITIVE = row(11).ToString
                                    amloName.FALSE_POSITIVE_EXPIRY_DT = row(12).ToString
                                    amloName.APPROVAL_STATUS = row(13).ToString
                                    amloName.SUB_ENTITY_ID = row(14).ToString
                                    amloName.CATEGORY_ = row(15).ToString
                                    amloName.HIGH_RISK_COUNTRY = row(16).ToString
                                    amloName.HIGH_RISK_COUNTRY_SCORE = row(17).ToString
                                    amloName.INFO_SOURCE = row(18).ToString
                                    amloName.ORG_ID = row(19).ToString
                                    amloName.ACTION = row(20).ToString
                                    amloName.ID_REF_NO = row(21).ToString
                                    amloName.BATCH_NO = row(22).ToString
                                    amloName.BATCH_DATE = row(23).ToString


                                    db18TblName.ENTITY_ID = row(0).ToString
                                    db18TblName.SYS_ID = maxName18TblSYS_ID
                                    db18TblName.NAME_TYPE = row(2).ToString

                                    If IsSpaceAlphaNumeric(firstName) Then
                                        db18TblName.FIRSTNAME_E = firstName
                                    Else
                                        db18TblName.FIRST_NAME = firstName
                                    End If
                                    If IsSpaceAlphaNumeric(middleName) Then
                                        db18TblName.MIDDLE_NAME_E = middleName
                                    Else
                                        db18TblName.MIDDLE_NAME = middleName
                                    End If
                                    If IsSpaceAlphaNumeric(surName) Then
                                        db18TblName.SURNAME_E = surName
                                    Else
                                        db18TblName.SURNAME = surName
                                    End If
                                    If IsSpaceAlphaNumeric(entityName) Then
                                        db18TblName.ENTITY_NAME_E = entityName
                                    Else
                                        db18TblName.ENTITY_NAME = entityName
                                    End If
                                    If IsSpaceAlphaNumeric(suffix) Then
                                        db18TblName.SUFFIX_E = suffix
                                    Else
                                        db18TblName.SUFFIX = suffix
                                    End If


                                    db18TblName.ENGLISH_NAME = row(8).ToString
                                    'db18TblName.SINGLE_STRING_NAME = row(9).ToString
                                    'db18TblName.ORIGINAL_SCRIPT_NAME = row(10).ToString
                                    If Not String.IsNullOrEmpty(row(10).ToString) AndAlso IsSpaceAlphaNumeric(row(10).ToString) Then
                                        db18TblName.SINGLE_STRING_NAME = row(10).ToString
                                    Else
                                        db18TblName.ORIGINAL_SCRIPT_NAME = row(10).ToString
                                    End If

                                    If Not String.IsNullOrEmpty(row(9).ToString) AndAlso IsSpaceAlphaNumeric(row(9).ToString) Then
                                        db18TblName.SINGLE_STRING_NAME = row(9).ToString
                                    Else
                                        db18TblName.ORIGINAL_SCRIPT_NAME = row(9).ToString
                                    End If

                                    db18TblName.FALSE_POSITIVE = row(11).ToString
                                    db18TblName.FALSE_POSITIVE_EXPIRY_DT = row(12).ToString
                                    db18TblName.APPROVAL_STATUS = row(13).ToString
                                    db18TblName.SUB_ENTITY_ID = row(14).ToString
                                    db18TblName.CATEGORY_ = row(15).ToString
                                    db18TblName.HIGH_RISK_COUNTRY = row(16).ToString
                                    db18TblName.HIGH_RISK_COUNTRY_SCORE = row(17).ToString
                                    db18TblName.INFO_SOURCE = row(18).ToString
                                    db18TblName.ORG_ID = row(19).ToString
                                    db18TblName.ACTION = row(20).ToString
                                    db18TblName.ID_REF_NO = row(21).ToString
                                    db18TblName.BATCH_NO = row(22).ToString
                                    db18TblName.BATCH_DATE = row(23).ToString

                                    SanctionListAll.AddToAMLO_NAME(amloName)
                                    SanctionListAll.AddToAMLO_NEW_NAME_TEMP(db18TblName)
                                End If
                            Next
                        End If
                    End If

                    Dim dtRelation = ds.Tables("relationship")
                    If Not dtRelation Is Nothing Then
                        If dtRelation.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtRelation.Rows.Count - 1)
                                Dim row As DataRow = dtRelation.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblRelation = New ADOEntitySanctionList.AMLO_NEW_RELATIONSHIP_TEMP
                                    amloRelation = New ADOEntitySanctionList.AMLO_RELATIONSHIP
                                    maxRelation18TblSYS_ID += 1

                                    amloRelation.ENTITY_ID = row(0).ToString
                                    amloRelation.SYS_ID = maxRelation18TblSYS_ID
                                    amloRelation.ASSOCIATE_ENTITY = row(2).ToString
                                    amloRelation.RELATIONSHIP_TYPE = row(3).ToString
                                    amloRelation.EX_RELATIONSHIP = row(4).ToString
                                    amloRelation.BATCH_NO = row(5).ToString
                                    amloRelation.BATCH_DATE = row(6).ToString

                                    db18TblRelation.ENTITY_ID = row(0).ToString

                                    db18TblRelation.SYS_ID = maxRelation18TblSYS_ID
                                    db18TblRelation.ASSOCIATE_ENTITY = row(2).ToString
                                    db18TblRelation.RELATIONSHIP_TYPE = row(3).ToString
                                    db18TblRelation.EX_RELATIONSHIP = row(4).ToString
                                    db18TblRelation.BATCH_NO = row(5).ToString
                                    db18TblRelation.BATCH_DATE = row(6).ToString

                                    SanctionListAll.AddToAMLO_RELATIONSHIP(amloRelation)
                                    SanctionListAll.AddToAMLO_NEW_RELATIONSHIP_TEMP(db18TblRelation)
                                End If
                            Next
                        End If
                    End If

                    Dim dtResident = ds.Tables("resident")
                    If Not dtResident Is Nothing Then
                        If dtResident.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtResident.Rows.Count - 1)
                                Dim row As DataRow = dtResident.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblResident = New ADOEntitySanctionList.AMLO_NEW_RESIDENT_TEMP
                                    amloResident = New ADOEntitySanctionList.AMLO_RESIDENT

                                    maxResident18TblSYS_ID += 1

                                    amloResident.ENTITY_ID = row(0).ToString
                                    amloResident.SYS_ID = maxResident18TblSYS_ID
                                    amloResident.TYPE = row(2).ToString
                                    amloResident.COUNTRY_TYPE = row(3).ToString
                                    amloResident.COUNTRY_CODE = row(4).ToString
                                    amloResident.COUNTRY_DESC = row(5).ToString
                                    amloResident.MAIN_ENTRY = GetValueOrBlankStringRowData(row, 6)
                                    amloResident.SUB_ENTITY_ID = row(7).ToString
                                    amloResident.BATCH_NO = row(8).ToString
                                    amloResident.BATCH_DATE = row(9).ToString

                                    db18TblResident.ENTITY_ID = row(0).ToString

                                    db18TblResident.SYS_ID = maxResident18TblSYS_ID
                                    db18TblResident.TYPE = row(2).ToString
                                    db18TblResident.COUNTRY_TYPE = row(3).ToString
                                    db18TblResident.COUNTRY_CODE = row(4).ToString
                                    db18TblResident.COUNTRY_DESC = row(5).ToString
                                    db18TblResident.MAIN_ENTRY = amloResident.MAIN_ENTRY
                                    db18TblResident.SUB_ENTITY_ID = row(7).ToString
                                    db18TblResident.BATCH_NO = row(8).ToString
                                    db18TblResident.BATCH_DATE = row(9).ToString

                                    SanctionListAll.AddToAMLO_RESIDENT(amloResident)
                                    SanctionListAll.AddToAMLO_NEW_RESIDENT_TEMP(db18TblResident)
                                End If
                            Next
                        End If
                    End If

                    Dim dtRole = ds.Tables("role")
                    If Not dtRole Is Nothing Then
                        If dtRole.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtRole.Rows.Count - 1)
                                Dim row As DataRow = dtRole.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblRole = New ADOEntitySanctionList.AMLO_NEW_ROLE_TEMP
                                    amloRole = New ADOEntitySanctionList.AMLO_ROLE

                                    maxRole18TblSYS_ID += 1

                                    amloRole.ENTITY_ID = row(0).ToString
                                    amloRole.SYS_ID = maxRole18TblSYS_ID
                                    amloRole.ROLE_TYPE = row(2).ToString
                                    amloRole.SINCE_DAY = row(3).ToString
                                    amloRole.SINCE_MONTH = row(4).ToString
                                    amloRole.SINCE_YEAR = row(5).ToString
                                    amloRole.TO_DAY = row(6).ToString
                                    amloRole.TO_MONTH = row(7).ToString
                                    amloRole.TO_YEAR = row(8).ToString
                                    amloRole.OCC_CATEGORY = row(9).ToString
                                    amloRole.OCC_TITLE = row(10).ToString
                                    amloRole.ROLE_LOCATION = row(11).ToString
                                    amloRole.BATCH_NO = row(12).ToString
                                    amloRole.BATCH_DATE = row(13).ToString

                                    db18TblRole.ENTITY_ID = row(0).ToString

                                    db18TblRole.SYS_ID = maxRole18TblSYS_ID
                                    db18TblRole.ROLE_TYPE = row(2).ToString
                                    db18TblRole.SINCE_DAY = row(3).ToString
                                    db18TblRole.SINCE_MONTH = row(4).ToString
                                    db18TblRole.SINCE_YEAR = row(5).ToString
                                    db18TblRole.TO_DAY = row(6).ToString
                                    db18TblRole.TO_MONTH = row(7).ToString
                                    db18TblRole.TO_YEAR = row(8).ToString
                                    db18TblRole.OCC_CATEGORY = row(9).ToString
                                    db18TblRole.OCC_TITLE = row(10).ToString
                                    db18TblRole.ROLE_LOCATION = row(11).ToString
                                    db18TblRole.BATCH_NO = row(12).ToString
                                    db18TblRole.BATCH_DATE = row(13).ToString

                                    SanctionListAll.AddToAMLO_ROLE(amloRole)
                                    SanctionListAll.AddToAMLO_NEW_ROLE_TEMP(db18TblRole)
                                End If
                            Next
                        End If
                    End If

                    Dim dtSanction = ds.Tables("sanction")
                    If Not dtSanction Is Nothing Then
                        If dtSanction.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtSanction.Rows.Count - 1)
                                Dim row As DataRow = dtSanction.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblSanction = New ADOEntitySanctionList.AMLO_NEW_SANCTION_TEMP
                                    amloSanction = New ADOEntitySanctionList.AMLO_SANCTION

                                    maxSanction18TblSYS_ID += 1

                                    amloSanction.ENTITY_ID = row(0).ToString
                                    amloSanction.SYS_ID = maxSanction18TblSYS_ID
                                    amloSanction.SINCE_DAY = row(2).ToString
                                    amloSanction.SINCE_MONTH = row(3).ToString
                                    amloSanction.SINCE_YEAR = row(4).ToString
                                    amloSanction.TO_DAY = row(5).ToString
                                    amloSanction.TO_MONTH = row(6).ToString
                                    amloSanction.TO_YEAR = row(7).ToString
                                    amloSanction.REFERENCE = row(8).ToString
                                    amloSanction.BATCH_NO = row(9).ToString
                                    amloSanction.BATCH_DATE = row(10).ToString

                                    db18TblSanction.ENTITY_ID = row(0).ToString

                                    db18TblSanction.SYS_ID = maxSanction18TblSYS_ID
                                    db18TblSanction.SINCE_DAY = row(2).ToString
                                    db18TblSanction.SINCE_MONTH = row(3).ToString
                                    db18TblSanction.SINCE_YEAR = row(4).ToString
                                    db18TblSanction.TO_DAY = row(5).ToString
                                    db18TblSanction.TO_MONTH = row(6).ToString
                                    db18TblSanction.TO_YEAR = row(7).ToString
                                    db18TblSanction.REFERENCE = row(8).ToString
                                    db18TblSanction.BATCH_NO = row(9).ToString
                                    db18TblSanction.BATCH_DATE = row(10).ToString

                                    SanctionListAll.AddToAMLO_SANCTION(amloSanction)
                                    SanctionListAll.AddToAMLO_NEW_SANCTION_TEMP(db18TblSanction)
                                End If
                            Next
                        End If
                    End If

                    Dim dtSource = ds.Tables("source")
                    If Not dtSource Is Nothing Then
                        If dtSource.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtSource.Rows.Count - 1)
                                Dim row As DataRow = dtSource.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblSource = New ADOEntitySanctionList.AMLO_NEW_SOURCE_TEMP
                                    amloSource = New ADOEntitySanctionList.AMLO_SOURCE

                                    maxSource18TblSYS_ID += 1

                                    amloSource.ENTITY_ID = row(0).ToString
                                    amloSource.SYS_ID = maxSource18TblSYS_ID
                                    amloSource.NAME = row(2).ToString
                                    amloSource.BATCH_NO = row(3).ToString
                                    amloSource.BATCH_DATE = row(4).ToString

                                    db18TblSource.ENTITY_ID = row(0).ToString

                                    db18TblSource.SYS_ID = maxSource18TblSYS_ID
                                    db18TblSource.NAME = row(2).ToString
                                    db18TblSource.BATCH_NO = row(3).ToString
                                    db18TblSource.BATCH_DATE = row(4).ToString

                                    SanctionListAll.AddToAMLO_SOURCE(amloSource)
                                    SanctionListAll.AddToAMLO_NEW_SOURCE_TEMP(db18TblSource)
                                End If
                            Next
                        End If
                    End If

                    Dim dtStr = ds.Tables("str")
                    If Not dtStr Is Nothing Then
                        If dtStr.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtStr.Rows.Count - 1)
                                Dim row As DataRow = dtStr.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblStr = New ADOEntitySanctionList.AMLO_NEW_STR_TEMP
                                    amloStr = New ADOEntitySanctionList.AMLO_STR

                                    maxStr18TblSYS_ID += 1

                                    amloStr.ENTITY_ID = row(0).ToString
                                    amloStr.SYS_ID = maxStr18TblSYS_ID
                                    amloStr.ACCOUNT_NO = row(2).ToString
                                    amloStr.REF_CASE_NO = row(3).ToString
                                    amloStr.REF_NOTICE_NO = row(4).ToString
                                    amloStr.DOC_TYPE = row(5).ToString
                                    amloStr.DOC_CONTROL_NO = row(6).ToString
                                    amloStr.ORG_ID = row(7).ToString
                                    amloStr.ORG_NAME = row(8).ToString
                                    amloStr.BATCH_NO = row(9).ToString
                                    amloStr.BATCH_DATE = row(10).ToString
                                    amloStr.DOWNLOADABLE_IND = row(11).ToString
                                    amloStr.PUBLIC_IND = row(12).ToString

                                    db18TblStr.ENTITY_ID = row(0).ToString

                                    db18TblStr.SYS_ID = maxStr18TblSYS_ID
                                    db18TblStr.ACCOUNT_NO = row(2).ToString
                                    db18TblStr.REF_CASE_NO = row(3).ToString
                                    db18TblStr.REF_NOTICE_NO = row(4).ToString
                                    db18TblStr.DOC_TYPE = row(5).ToString
                                    db18TblStr.DOC_CONTROL_NO = row(6).ToString
                                    db18TblStr.ORG_ID = row(7).ToString
                                    db18TblStr.ORG_NAME = row(8).ToString
                                    db18TblStr.BATCH_NO = row(9).ToString
                                    db18TblStr.BATCH_DATE = row(10).ToString
                                    db18TblStr.DOWNLOADABLE_IND = row(11).ToString
                                    db18TblStr.PUBLIC_IND = row(12).ToString

                                    SanctionListAll.AddToAMLO_STR(amloStr)
                                    SanctionListAll.AddToAMLO_NEW_STR_TEMP(db18TblStr)
                                End If
                            Next
                        End If
                    End If

                    Dim dtVessel = ds.Tables("vessel")
                    If Not dtVessel Is Nothing Then
                        If dtVessel.Rows.Count > 0 Then
                            For i As Integer = 0 To (dtVessel.Rows.Count - 1)
                                Dim row As DataRow = dtVessel.Rows(i)
                                If Not String.IsNullOrEmpty(row(0).ToString) Then
                                    db18TblVessel = New ADOEntitySanctionList.AMLO_NEW_VESSEL_TEMP
                                    amloVessel = New ADOEntitySanctionList.AMLO_VESSEL

                                    maxVessel18TblSYS_ID += 1

                                    amloVessel.ENTITY_ID = row(0).ToString
                                    amloVessel.SYS_ID = maxVessel18TblSYS_ID
                                    amloVessel.VESSEL_CALL_SIGN = row(2).ToString
                                    amloVessel.VESSEL_TYPE = row(3).ToString
                                    amloVessel.VESSEL_TONNAGE = row(4).ToString
                                    amloVessel.VESSEL_GRT = row(5).ToString
                                    amloVessel.VESSEL_OWNER = row(6).ToString
                                    amloVessel.VESSEL_FLAG = row(7).ToString
                                    amloVessel.BATCH_NO = row(8).ToString
                                    amloVessel.BATCH_DATE = row(9).ToString

                                    db18TblVessel.ENTITY_ID = row(0).ToString

                                    db18TblVessel.SYS_ID = maxVessel18TblSYS_ID
                                    db18TblVessel.VESSEL_CALL_SIGN = row(2).ToString
                                    db18TblVessel.VESSEL_TYPE = row(3).ToString
                                    db18TblVessel.VESSEL_TONNAGE = row(4).ToString
                                    db18TblVessel.VESSEL_GRT = row(5).ToString
                                    db18TblVessel.VESSEL_OWNER = row(6).ToString
                                    db18TblVessel.VESSEL_FLAG = row(7).ToString
                                    db18TblVessel.BATCH_NO = row(8).ToString
                                    db18TblVessel.BATCH_DATE = row(9).ToString

                                    SanctionListAll.AddToAMLO_VESSEL(amloVessel)
                                    SanctionListAll.AddToAMLO_NEW_VESSEL_TEMP(db18TblVessel)
                                End If
                            Next
                        End If
                    End If

                    SanctionListAll.SaveChanges()
                End If
            End If
        Catch ex As Exception
            Throw New Exception("เกิดข้อผิดพลาดในการแปลงไฟล์")
        End Try

        Return mStrSactionType
    End Function

    Private Function GetValueOrBlankStringRowData(ByVal row As DataRow, ByVal rowNum As Integer, Optional ByVal defVal As Integer = -1) As Integer
        If (row.IsNull(rowNum)) Then
            Return defVal
        Else
            Return CInt(row(rowNum).ToString)
        End If
    End Function

    Private Function TryExactOFACDate2Date(ByRef rawDate As String) As Date
        Dim sam As String = rawDate
        Dim resDate As New Date
        If Not String.IsNullOrEmpty(sam) Then
            If Not sam.ToLower.IndexOf("circa") = -1 Then
                sam = sam.Replace("circa ", "")
            End If

            Dim resParse As Boolean = False


            resParse = DateTime.TryParseExact(sam, _
   "dd MMM yyyy", CultureInfo.InvariantCulture, _
       DateTimeStyles.None, resDate)

            If Not resParse Then
                resParse = DateTime.TryParseExact(sam, _
  "MMM yyyy", CultureInfo.InvariantCulture, _
      DateTimeStyles.None, resDate)

                If Not resParse Then
                    resDate = Nothing
                    ' yyyy
                    'resDateStr.Append(sam)
                    Return Nothing
                Else
                    ' mmm yyyy
                    'resDateStr.Append(resDate.Month).Append("/").Append(resDate.Year)
                    Return Nothing
                End If

            Else
                ' dd mmm yyyy
                Return resDate
            End If

        Else
            Return Nothing
        End If
    End Function

    Private Function TryExactOFACDate(ByRef rawDate As String) As String
        Dim sam As String = rawDate
        If Not String.IsNullOrEmpty(sam) Then
            If Not sam.ToLower.IndexOf("circa") = -1 Then
                sam = sam.Replace("circa ", "")
            End If
            Dim resDate As New Date
            Dim resParse As Boolean = False
            Dim resDateStr As New StringBuilder

            resParse = DateTime.TryParseExact(sam, _
   "dd MMM yyyy", CultureInfo.InvariantCulture, _
       DateTimeStyles.None, resDate)

            If Not resParse Then
                resParse = DateTime.TryParseExact(sam, _
  "MMM yyyy", CultureInfo.InvariantCulture, _
      DateTimeStyles.None, resDate)

                If Not resParse Then
                    resDate = Nothing
                    ' yyyy
                    'resDateStr.Append(sam)
                    Return Nothing
                Else
                    ' mmm yyyy
                    'resDateStr.Append(resDate.Month).Append("/").Append(resDate.Year)
                    Return Nothing
                End If

            Else
                ' dd mmm yyyy
                resDateStr.Append(resDate.Day).Append("/").Append(resDate.Month).Append("/").Append(resDate.Year)
                Return resDateStr.ToString
            End If

        Else
            Return Nothing
        End If
    End Function

    Private Function TryExtractEUDate(ByRef RawDate As String, ByRef resDate As Date, ByRef resYear As String()) As Boolean

        Dim strRawDate = RawDate
        Dim resStrYear(2) As String
        resDate = Nothing

        Dim betweenWordIndex As Integer = strRawDate.ToLower.IndexOf("between")
        If betweenWordIndex = 0 Then
            Dim strExtractDate As String() = strRawDate.Split(" ") 'get (1) and (3)
            resStrYear(0) = strExtractDate(1)
            resStrYear(1) = strExtractDate(3)
        Else
            Dim strExtractDate As String() = strRawDate.Split(" ")
            Dim strExtractDateWithDash As String() = strExtractDate(0).Split("-")
            If strExtractDateWithDash.Length = 3 Then
                ' normal 
                DateTime.TryParseExact(strExtractDate(0), _
                "yyyy-MM-dd", CultureInfo.InvariantCulture, _
                    DateTimeStyles.None, resDate)

                resStrYear(0) = strExtractDate(0).Split("-")(2)

            ElseIf strExtractDateWithDash.Length = 2 Then
                strExtractDate = strExtractDate(0).Split("-")
                Dim year1 As String = strExtractDate(0)
                Dim year2 As String = strExtractDate(1)

                resStrYear(0) = year1
                resStrYear(1) = year2
            Else
                Dim ans As String = strExtractDate(0)
                If ans.Split("/").Length = 3 Then

                    Dim dotYearStyle As Boolean = DateTime.TryParseExact(ans, _
"yyyy/MM/dd", CultureInfo.InvariantCulture, _
DateTimeStyles.None, resDate)
                    Dim res As String = resDate.Year
                ElseIf ans.Split(".").Length = 3 Then

                    Dim dotYearStyle As Boolean = DateTime.TryParseExact(ans, _
"yyyy.M.d", CultureInfo.InvariantCulture, _
DateTimeStyles.None, resDate)
                    If Not dotYearStyle Then
                        dotYearStyle = DateTime.TryParseExact(ans, _
"d.M.yyyy", CultureInfo.InvariantCulture, _
DateTimeStyles.None, resDate)
                        If dotYearStyle Then
                            resYear(0) = resDate.Year
                        Else
                            Return False
                        End If
                    Else
                        resYear(0) = resDate.Year
                    End If
                Else
                    ' assume This only year
                    resYear(0) = ans
                End If
            End If
        End If


        resYear = resStrYear
        Return True
    End Function

    Private Function TryExtractUNDate(ByVal rawDate As String) As Date

        If Not String.IsNullOrEmpty(rawDate) Then
            Dim spltDate As String() = rawDate.Split("T")

            Dim resDate As New Date
            Dim resParse As Boolean = Date.TryParseExact(spltDate(0), _
"yyyy-MM-dd", CultureInfo.InvariantCulture, _
DateTimeStyles.None, resDate)

            If resParse Then
                Return resDate
            Else
                Return Nothing
            End If
        End If
        Return Nothing

    End Function

    Private Function convertCountryCode(ByRef countryCode As String) As String
        countryCode = countryCode.ToUpper

        Dim cultures() As CultureInfo = CultureInfo.GetCultures(CultureTypes.SpecificCultures)
        For Each culture As CultureInfo In cultures

            Dim region As New RegionInfo(culture.LCID)
            If (region.ThreeLetterISORegionName.ToUpper.Equals(countryCode)) Then
                Return region.EnglishName
            End If
        Next

        Return Nothing

    End Function

    Private Sub extractEUNameCard(ByVal strRawIDNumber As String, ByRef strIDNumber As String, ByRef strIDType As String)
        'Dim strTest1 As String = "D 00857 (passport issued on 1997-11-20)"
        'Dim strTest2 As String = "AD001159"
        'Dim strTest3 As String = "PD – AE 065FH08"
        'Dim strTest4 As String = "AE/46CR05 [Old passport]"
        'Dim strTest5 As String = "0018061/104 (issued 12 September 1993)"
        'Dim strTest6 As String = "44103-5251752-5 (National Identification No."

        ' case 1 : case with ( , [ , {

        Dim parentheses As Integer = strRawIDNumber.IndexOf("(")
        Dim basket As Integer = strRawIDNumber.IndexOf("[")
        Dim bracket As Integer = strRawIDNumber.IndexOf("{")

        If Not parentheses = -1 Or Not basket = -1 Or Not bracket = -1 Then
            Dim strSplitSpace() As String = strRawIDNumber.Split(" ")
            If strSplitSpace.Length > 0 Then

                If parentheses > 0 Then
                    strIDNumber = Regex.Replace(strRawIDNumber.Substring(0, parentheses), "[^A-Za-z0-9]", "")
                    strIDType = strRawIDNumber.Substring(parentheses, strRawIDNumber.Length - parentheses)
                ElseIf basket > 0 Then
                    strIDNumber = Regex.Replace(strRawIDNumber.Substring(0, basket), "[^A-Za-z0-9]", "")
                    strIDType = strRawIDNumber.Substring(basket, strRawIDNumber.Length - basket)
                Else
                    strIDNumber = Regex.Replace(strRawIDNumber.Substring(0, bracket), "[^A-Za-z0-9]", "")
                    strIDType = strRawIDNumber.Substring(bracket, strRawIDNumber.Length - bracket)
                End If


                If Not strIDType.ToLower.IndexOf("passport") = -1 Then
                    strIDType = "Passport"
                ElseIf Not strIDType.ToLower.IndexOf("id") = -1 Or Not strIDType.ToLower.IndexOf("identification") = -1 Then
                    strIDType = "Identification Number"
                Else
                    strIDType = "Other"
                End If
            Else

            End If
        Else  ' case 2 : another case from case 1
            If Not strRawIDNumber.ToLower.IndexOf("passport") = -1 Then
                strIDType = "Passport"
            ElseIf Not strRawIDNumber.ToLower.IndexOf("id") = -1 Or Not strRawIDNumber.ToLower.IndexOf("identification") = -1 Then
                strIDType = "Identification Number"
            Else
                strIDType = "Other"
            End If
            strIDNumber = Regex.Replace(strRawIDNumber, "[^A-Za-z0-9]", "")
        End If

        ' case 1_1 : Can split with " "
        ' case 1_2 : another case from 1_1
        ' case 1_2_1 : check word 
    End Sub

    Private Function removeSpecialChar(ByVal strRaw As String) As String
        Return Regex.Replace(strRaw, "[^A-Za-z0-9]", "")
    End Function

    Private Function IsSpaceAlphaNumeric(ByVal sText As String) As Boolean
        'IsSpaceAlphaNumeric = Not (sText Like "*[! 0-9A-Za-z().,;-]*")
        IsSpaceAlphaNumeric = Not (sText Like "*[! 0-9A-Za-z()\/&.,;-]*")
        '[! 0-9A-Za-z()[]{}\/&.,;-]
        'Жанна/Алексеевна/Жуковская
    End Function

End Class
